在VBA中循环使用具有类似名称结构的工作表

My Excel file has the following structure of Worksheets:

我的Excel文件具有以下工作表结构:

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

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

So as you can see 4 times A, 3 times B, 5 times C etc (no even distribution)

所以你可以看到4次A,3次B,5次C等(没有均匀分布)

What I would like to do is loop through groups of worksheets and apply some code. In this case it must be the same code within groups but different code between groups, therefore I cannot simply loop through all worksheets at the same time.

我想做的是循环工作表组并应用一些代码。在这种情况下,它必须是组内的相同代码,但组之间的代码不同,因此我不能简单地同时遍历所有工作表。

I know how to get the names of the Worksheets in VBA. My first idea was to first 'cut off' the numbers (last character) from the right of the groupnames and then to identify unique groups of the results. Then I wanted to loop for each group, e.g. the first loop would start in A1 and stop at A4. But how can I tell VBA to identify an upper boundary in name that is not constant (in the example A4, then B3, then C5 etc.)?

我知道如何在VBA中获取工作表的名称。我的第一个想法是首先从组名的右侧“切断”数字(最后一个字符),然后识别结果的唯一组。然后我想循环每个组,例如第一个循环将从A1开始,然后停在A4。但是我怎么能告诉VBA识别名义上不是常数的上边界(在例子A4中,然后是B3,然后是C5等)?

Maybe that is not the most efficient way either. I could even rename all my worksheets to a different system if that would make any sense, but coding has to be applied in any case. Any ideas are highly appreciated.

也许这不是最有效的方式。如果有任何意义,我甚至可以将我的所有工作表重命名为不同的系统,但无论如何都必须应用编码。任何想法都受到高度赞赏。

What I want to do, in short:

我想做什么,简而言之:

1) Identify unique groups of Worksheeps by name (A, B, C in the example above)

1)按名称识别唯一的Worksheep组(上例中的A,B,C)

2) For each group, loop through all associated sheets and apply some code

2)对于每个组,循环遍历所有关联的工作表并应用一些代码

Thanks.

3 个解决方案

#1

Yet another way.. if your names are just A - Z

还有另一种方式..如果你的名字只是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

#2

For identifying unique groups, you could do a loop that looks at the worksheet name, and if it has an "A", do X, "B", do Y, etc.

为了识别唯一的组,你可以做一个查看工作表名称的循环,如果它有一个“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

You could then create other macros for each sheet type and call that in the above code. i.e.:

然后,您可以为每种工作表类型创建其他宏,并在上面的代码中调用它。即:

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

Edit: For the part regarding only wanting to do this on certain sheets, or setting some upper boundary...If you know exactly what sheets you want to run the code on, you could instead put them into an Array, then run the code only on sheets in that array.

编辑:对于只想在某些工作表上执行此操作或设置某些上边界的部分...如果您确切知道要运行代码的工作表,则可以将它们放入数组中,然后运行代码仅在该数组中的工作表上。

Something like (psuedocode):

像(伪代码)的东西:

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

#3

I'd suggest to use Dictionary to define groups and count of sheet in each group.

我建议使用Dictionary来定义每组中的组和表的数量。

Here is an idea:

这是一个想法:

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

Result:

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

#1