CODEWITHSUNDEEP
vbaexcel
MikeisWright
MikeisWright

Reputation: 13

Excel VBA - Loop through the spreadsheets and preform another loop

I'm probably just doing something wrong.

I'm trying to loop through each worksheet In a work book, and on each worksheet I want it to preform another loop. The problem is that I doesn't move onto the next worksheet and only works on the active one. Any help would be appreciated.

Sub Hours()     
    Dim ws As Worksheet
    Dim I As Integer

        For Each ws In ActiveWorkbook.Worksheets
            For I = 6 To 21

                k = I + 1

                If Cells(k, 7).Value = "" Then

                ElseIf Cells(I, 8).Value <> Cells(k, 7) Then
                     Cells(I, 8).Font.Color = vbRed
                     Cells(k, 7).Font.Color = vbRed
                End If
            Next I
    Next ws     
End Sub

Upvotes: 1

Views: 98

Answers (4)

ASH
ASH

Reputation: 20322

Consider this.

Sub Test1()
'UpdatebyExtendoffice20161222
      Dim x As Integer
      Dim ws As Worksheet

      ' Begin looping through the sheets
      For Each ws In ActiveWorkbook.Worksheets
      ws.Select
        ' Begin looping through the cells
        ' Set numrows = number of rows of data.
        NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
        ' Select cell a1.
        Range("A1").Select
        ' Establish "For" loop to loop "numrows" number of times.
        For x = 1 To NumRows
           ' Insert your code here.
           ' Selects cell down 1 row from active cell.
           ActiveCell.Offset(1, 0).Select

           ' After a blank cells is found, move to the next sheet
        Next

      Next ws

End Sub

Upvotes: 0

Jarom
Jarom

Reputation: 1077

You have to tell the vba to select the next worksheet. Right now it is looping through the number of worksheets you have, but since there is nothing in the code that activates the next worksheets it just loops on the first one. Try this:

Sub Hours()
Dim ws As Worksheet
Dim I As Integer

    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        For I = 6 To 21

            k = I + 1

            If Cells(k, 7).Value = "" Then

            ElseIf Cells(I, 8).Value <> Cells(k, 7) Then
                 Cells(I, 8).Font.Color = vbRed
                 Cells(k, 7).Font.Color = vbRed
            End If
        Next I
Next ws

End Sub

Just to give you a little extra information, I usually use this code to handle looping through worksheets:

sub example()
ws = ThisWorkbook.Worksheets.Count
for i = 1 to ws
'other code
next i
end sub

What I like about this way of doing it is that I have more flexibility to not necessarily loop through all worksheets. Often I don't want to execute a process on the first or last worksheet. To accomplish that I can adjust the for statement like this:

'to not loop through the last worksheet
for i = 1 to ws - 1

'to not loop through the first worksheet
for i = 2 to ws

I like this way for more flexibility. Of course if you want to loop through every worksheet using the for each ws method is simpler since you don't have to define a variable. I just wanted to expand on the subject.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, use the With ws statement to get all objects, such as Cells qualified with the current Worksheet you are looping through.

Code

Sub Hours()

Dim ws As Worksheet
Dim i As Long, k As Long

For Each ws In ActiveWorkbook.Worksheets
    With ws
        For i = 6 To 21
            'k = i + 1 ' not needed, just change k to i + 1
            ' merge 2 Ifs to one using an And
            If .Cells(i + 1, 7).Value <> "" And .Cells(i, 8).Value <> .Cells(i + 1, 7) Then
                 .Cells(i, 8).Font.Color = vbRed
                 .Cells(i + 1, 7).Font.Color = vbRed
            End If
        Next i
    End With
Next ws

End Sub

Upvotes: 3

MisterBic
MisterBic

Reputation: 307

I use this to loop through my worksheet :

     For i = 1 To ActiveWorkbook.Worksheets.Count
       ActiveWorkbook.Worksheets(i)
       'adapt your code into that
     Next i

It may work better for you

Upvotes: 0

Related Questions