遍历所有工作表VBA [英] Looping through all worksheets VBA

问题描述

我试图遍历activeworkbook中的所有工作表以执行重复性任务.

I am trying to loop through all the worksheets in the activeworkbook to perform a repetitive task.

我目前有以下代码:

Sub sort_sectors()

Dim i As Integer
Dim rng As Range
Dim SortRng As Range
Dim rng1 As Integer
Dim ws As Worksheet
Dim wb As Workbook
Dim LastCol As Long
Dim LastRow As Long

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets

'This is marking several of the sheets of which I do not want to run the sub
If ws.Range("a9").Value = "x" Then
NextIteration:
End If

'Reference point is rng1 to select the desired range
With Range("a1:t100")
    rng1 = .Find(what:="sector", LookIn:=xlValues).Row
End With

'return the row number for the sector header
LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column
LastRow = ws.Range("a15").End(xlDown).Row

'I am going to add the code below to finish out the task that I want to complete

Next

End Sub

我确定问题是我误解了for每个循环的实际工作方式.希望有人的答案能使您更好地理解.

I am sure the problem is that I'm misunderstanding something about how the for each loop actually works. Hopefully someone's answer will allow to better understand.

我真的很感谢您的帮助.

I really appreciate any help on this.

我对代码进行了一些编辑，但实际上我确实有一个错误:)我尝试对代码的"with ws.range etc ..."部分进行建议的更改，然后得到对象错误91.

I made some edits to the code, and now I actually do have an error :) I tried making the changes you suggested for the "with ws.range etc..." piece of the code, and I get the object error 91.

下面是我的新代码，经过改进".

Below is my new and "improved" code.

Sub sort_sectors()

Dim i As Integer
Dim rng As Range
Dim SortRng As Range
Dim intAnchorRow As Integer
Dim intMktCapAnchor As Integer
Dim intSectorAnchor As Integer
Dim ws As Worksheet
Dim wb As Workbook
Dim LastCol As Long
Dim LastRow As Long

Set wb = ActiveWorkbook

For Each ws In ActiveWorkbook.Worksheets

'Filter out the sheets that we don't want to run
If ws.Range("a9").Value <> "x" Or ws.Name = "__FDSCACHE__" Or ws.Name = "INDEX" Then

'Get the anchor points for getting sort range and the sort keys
''''''THIS IS THE PART THAT IS NOW GIVING ME THE ERROR'''''''
    With ws.Range("a1:t100")
        intAnchorRow = .Find(what:="sector", LookIn:=xlValues).Row
        intSectorAnchor = .Find(what:="sector", LookIn:=xlValues).Column
        intMktCapAnchor = .Find(what:="Market Cap", LookIn:=xlValues).Column
    End With

'Find the last row and column of the data range
    LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column
    LastRow = ws.Range("a15").End(xlDown).Row

    Set SortRng = Range(Cells(intAnchorRow + 1, 1), Cells(LastRow, LastCol))
    Range(SortRng).Sort key1:=Range(Cells(intAnchorRow + 1, intSectorAnchor), Cells(LastRow, intSectorAnchor)), _
        order1:=xlAscending, key2:=Range(Cells(intAnchorRow + 1, intMktCapAnchor), Cells(LastRow, intMktCapAnchor)), _
        order2:=xlDescending, Header:=xlNo


End If

Next

End Sub

再次感谢.这对我非常有帮助.

Thanks again. This has been very helpful for me.

推荐答案

如果我已正确理解您的问题，则您不想在单元格中使用带有 x 的工作表A9 .

If I've understood your issue correctly, you don't want to use a worksheet with an x in cell A9.

如果是这种情况，我将更改 if 语句的条件，以检查单元格是否不包含 x .如果是这样，它将输入其余的代码.如果不是，则转到 next 迭代.

If that's the case I would change the condition of the if statement to check if the cell does not contain the x. If this is true, it enters the rest of the code. If not, it goes to the next iteration.

此外，您的 NextIteration:在 If 语句中不执行任何操作.

Also, your NextIteration: doesn't do anything in the If statement.

Sub sort_sectors()

Dim i As Integer
Dim rng As Range
Dim SortRng As Range
Dim rng1 As Integer
Dim ws As Worksheet
Dim wb As Workbook
Dim LastCol As Long
Dim LastRow As Long

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets

    'This is marking several of the sheets of which I do not want to run the sub
    If ws.Range("a9").Value <> "x" Then

        'Reference point is rng1 to select the desired range
        With Range("a1:t100")
            rng1 = .Find(what:="sector", LookIn:=xlValues).Row
        End With

        'return the row number for the sector header
        LastCol = ws.Cells(20, ws.Columns.Count).End(xlToLeft).Column
        LastRow = ws.Range("a15").End(xlDown).Row

        'I am going to add the code below to finish out the task that I want to complete

    End If
Next    
End Sub

:运算符用于在 goto 调用之后将代码返回到该行.

The : operator is used to return the code to that line after a goto call.

例如

sub gotoEx()

for i = 1 to 10
    if i = 5 then
        goto jumpToHere
    end if
next i

jumpToHere: '<~~ the code will come here when i = 5
    'do some more code

end sub

当然，您可以根据需要在代码中使用此结构，并在 next

And of course you can use this structure in your code if you wish, and have the jumpToHere: line just before the next

例如

for each ws in wb.Worksheets
    if ws.Range("a9").Value = "x" then
        goto jumpToHere
    end if

    'the rest of your code goes here

jumpToHere:
next

这篇关于遍历所有工作表VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！