遍历工作表与VBA类似名称的结构

Question

我的Excel文件有工作表的结构如下：

A1A2A3A4B1B2B3C1C2C3C4C5 ...

因此，大家可以看到4次A，3次B，5 C时代等（没有均匀分布）

我会喜欢做的是循环工作表组并应用一些代码。在这种情况下，它必须是组内相同的代码，但组间代码不同，因此我不能简单地在所有工作表中循环。

我知道如何获取VBA中工作表的名称。我的第一个想法是首先从组名右边删除数字（最后一个字符），然后确定唯一的结果组。然后我想循环每组，例如第一个循环将从A1开始并停在A4。但是，我怎样才能让VBA识别名称上的不常数上限（例如A4，然后是B3，然后是C5等）？

也许这不是最有效的方法。我甚至可以将我的所有工作表重命名为不同的系统，如果这样做有任何意义的话，但是编码必须在任何情况下应用。任何想法都非常感谢。

我想要做什么，在短：

1）以名称（A，B，C在上面的示例中）

2）对于每个组，遍历所有识别Worksheeps的唯一的组关联表单并应用一些代码

谢谢。

Answer 1

另一种方式..如果你的名字只是A - Z

Sub DoStuff() 
Dim i As Integer 
Dim counter As Integer 
'loop through A to Z 
For i = 65 To 90 
    counter = 1 
    'loop until we no longer have a valid sheet 
    Do While isWorksheet(Chr(i) + CStr(counter)) = True 
     'do work by calling the correct method 
     Run (setSubName(Chr(i))) 
     counter = counter + 1 
    Loop 
Next i 
End Sub 

'check to see if the worksheet exists 
Function isWorksheet(name As String) As Boolean 
On Error GoTo wsError 
Err.Clear 
'just try and access the name 
Sheets(name).name 

wsError: 
    If Err.Number = 0 Then 
     isWorksheet = True 
    Else 
     isWorksheet = False 
    End If 
End Function 


'set the sub routine name to call 
Function setSubName(value As String) As String 
    setSubName = Switch(value = "A", "Sub_A_Name", value = "B", "Sub_B_Name", _ 
    value = "C", "Sub_C_Name", value = "D", "Sub_D_Name") 
End Function 


Sub Sub_A_Name() 
    'do work for group A 
End Sub 

Answer 2

为了识别独特的群体，你可以做一个循环，着眼于工作表名称，如果它有一个“A”，做X，“B”，做Y，等

Dim ws as Worksheet 

For each ws in Activebook.Sheets 
    If ws.name like "A*" Then 
     ** Code for "A" worksheets 
    Else If ws.name like "B" Then 
     ** code for "B*" worksheets 
    Else If [...] 
    End if 

Next ws 

你然后可以为每个表单类型创建其他宏，并在上面的代码中调用该宏。即：

Private Sub A_Things() 
    msgbox("This is a sheet 'A' type") 
    [...whatever other code you want] 
End Sub 

Private Sub B_Things() 
msgbox("This is a sheet 'B' type") 
[...whatever other code you want] 
End Sub 

Sub checkSheets() 

    Dim ws as Worksheet 

    For each ws in Activebook.Sheets 
     If ws.name like "A*" Then 
      Call A_Things 
     Else If ws.name like "B" Then 
      Call B_Things 
     Else If [...] 
     End if 
    Next ws 
End Sub 

编辑：有关只想要做到这一点对某些表，或者设置一些上限......如果你知道你想上运行的代码到底是什么表的一部分，你可以代替放他们到一个数组中，然后仅在该数组中的工作表上运行代码。

类似的信息（伪码）：

Dim nameArray() as Variant 
ReDim nameArray(4) 'Note, this can hold 5 values, so if you have X sheets, ReDim this to X-1 sheets 

nameArray = Array("A1","A2","A4","B1","B3") 

for i = 0 to UBound(nameArray()) 
    'this will loop through Sheet A1, then A2, then A4, etc. and run the code below 
    If nameArray(i) = "A1" Then 
     [run A1 code] 
    ElseIf [...] 
    End If 
Next i 

Answer 3

我建议使用Dictionary定义每个组中的基团和片材的计数。

这里有一个想法：

Sub LoopThroughGroupsOfSheets() 
'Needs reference to MS Scripting Runtime 
Dim dic As Dictionary 
Dim i As Integer, k As Integer 
Dim wshName As String 

'define Sheets for loop and count of them 
Set dic = New Dictionary 
dic.Add "A", 4 
dic.Add "B", 3 
dic.Add "C", 5 

For k = 0 To dic.Count - 1 
    For i = 1 To dic.Items(k) 
     DoSomething dic.Keys(k) & i 
    Next 
Next 

End Sub 

Sub DoSomething(wshName As String) 

    Debug.Print wshName 

End Sub 

结果：

SheetName: A1 
SheetName: A2 
SheetName: A3 
SheetName: A4 
SheetName: B1 
SheetName: B2 
SheetName: B3 
SheetName: C1 
SheetName: C2 
SheetName: C3 
SheetName: C4 
SheetName: C5