I cannot get my macro to loop through columns to perform the action I need-CodePudding

I have created a macro that checks row 3 in column B for two different criteria and if either are met, it puts the letter "R" in empty cells in the range B5:B20. If all are empty it dumps the word "No" in cell B34. I need this to do the same operation in columncs C to AF but I can't get it to loop. Any help would be appreciated. Thank you.

Sub Column_B()
    Dim ws As Worksheet
    Dim b As Range
    Set ws = Worksheets("January")
    For Each b In Range("B5:B20")
        If b.Value = 0 And ws.Range("b3") = ws.Range("a34") Or b.Value = 0 And ws.Range("b3") = ws.Range("a35") Then
            b.Value = "R"
        Else
            ws.Range("b34") = "No"
        End If
    Next b
End Sub

CodePudding user response：

Iterate across the columns B to AF in row 3 and use offsets to loop through the rows 5 to 20.

Option Explicit

Sub Column_BtoAF()

    Dim ws As Worksheet, r As Long
    Dim cell As Range, c As Range, bEmpty As Boolean
    
    Set ws = Worksheets("January")
    For Each c In Range("B3:AF3").Cells
        If c.Value2 = ws.Range("A34").Value2 _
           Or c.Value2 = ws.Range("A35").Value2 Then
        
            bEmpty = True
            For r = 5 To 20
                Set cell = c.Offset(r - 3)
                If IsEmpty(cell) Then
                    cell.Value = "R"
                Else
                    bEmpty = False
                End If
            Next
            If bEmpty Then c.Offset(31) = "No" ' row 34
            
        End If
    Next
    MsgBox "Done"
End Sub