Looping through all worksheets VBA

Question

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

I currently have the code below:

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

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.

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.

Answer 1

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

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.

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

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

For example

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

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

e.g.

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