How to loop thru an excel workbook using VBA

Question

I'm attempting to loop thru each sheet in an excel workbook. My code is only running thru the first sheet and it does not recognize where the last row is. Any help would be greatly appreciated.

Private Sub calculateValue()

For i = 2 To Rows.Count

    If Cells(i, 4).Value < 10 Then
        Cells(i, 6).Value = 20
    End If

    If Cells(i, 4).Value > 9 And Range("D2").Value < 40 Then
        Cells(i, 6).Value = 70
    End If

    If Cells(i, 4).Value > 39 Then
        Cells(i, 6).Value = 120
    End If


    If Cells(i, 4).Value = Empty Then Exit For

Next i

Cells(i, 6).Value = ""

End Sub

There appears to be no attempt at looping through the sheets. And Rows.count is c\going to return the max number of rows on the sheet as in all of them, all 1.04 million. There are plenty of answers on this site alone on how to loop through sheets and find the last row with data. Have you tried googling those two specifically? — Scott Craner

Marc Marc · Accepted Answer · 2016-02-03T21:28:23

This first sub below iterates over each worksheet. The second is a placeholder for your code.

Sub forEachWs()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call calculateValue(ws)
    Next
End Sub

Sub calculateValue(ws As Worksheet)
    'Worksheets(ws).Activate
    'iterate over cells
    'your code goes here
End Sub

How to loop thru an excel workbook using VBA

1 Answers