CODEWITHSUNDEEP
excelvba
Starbucks
Starbucks

Reputation: 135

Looping over every row until last line of data

I have three macros that work for my first row. I want to create a loop to make that code run for all the rows until the last row of input.

It prints what is missing when an empty cell is found in columns B, D and E. (I will eventually put the three macros into one to make it more concise.)

This is the code for the first row:

'checking if PayorID is empty
Sub error_field()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("A2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & "PayorID"
    End If
End Sub

'checking if TIN is empty
Sub error_field2()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("E2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & ", TIN"
    End If
End Sub

'checking if AccountNo is empty
Sub error_field3()
    Sheets("1099-Misc_Form_Template").Select
    Range("A2").Select
    If Range("F2").Value = "" Then
    ActiveCell.Value = ActiveCell.Value & ", AccountNo"
    End If
End Sub

This is what I tried for a loop:

'repeating for all rows
Sub repeat_all_rows()
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer

RowCount = 0

Set sh = ActiveSheet
For Each rw In sh.Rows

    If Range("A2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & "PayorID"
    If Range("E2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & ", TIN"
    If Range("F2").Value = "" Then
        ActiveCell.Value = ActiveCell.Value & ", AccountNo"
        Exit For
    End If

    RowCount = RowCount + 1

Next rw

End Sub

I want code to be performed on all the rows until the last row of data is found.

Upvotes: 0

Views: 561

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Something like this should work:

'repeating for all rows
Sub repeat_all_rows()

    Dim sh As Worksheet
    Dim rw As Range
    Dim RowCount As Integer

    RowCount = 0

    Set sh = ActiveSheet
    For Each rw In sh.UsedRange.Rows

        FlagMissing rw, "A", "Payor ID"
        FlagMissing rw, "E", "TIN"
        FlagMissing rw, "F", "AccountNo"

    Next rw

End Sub

Sub FlagMissing(rw As Range, col as String, Flag As String)
    If Len(Trim(rw.cells(1, col).value)) = 0 Then
        With rw.Cells(1)
            'add the flag with a comma if there's already content there
            .Value = .Value & IIf(.Value="", "", ", ") & Flag
        End with
    End If
End sub

...though I've not accounted for that Exit For in your posted code.

Upvotes: 1

Related Questions