If cell in range doesn't equal blank then apply string to cell in another range

Question

I have two ranges. ("A2:A81,H2:H81") and ("E2:E81,L2:L81")

("A2:A81,H2:H81") = search range and
("E2:E81,L2:L81") = string range

I have range (A2:A81,H2:H81) where I'm looking to see if a cell in the range is not blank. If one of those cells in this range is not blank and a cell in the "string range" is blank I want to put the string "No Punch" in the cell for that range.

Ex: If cell A10 is not blank and E10 is blank put the string "No Punch" when running the macro.

This is broken. I can use just the for each statement (without the if) but it fills all cells in the range that are blank and doesn't check the other range.

Sub FinishSheet()

    If (Worksheets("GerriSheet").Range("A2:A81,H2:H81").Value = "") Then

    For Each cell In Range("E2:E81,L2:L81")
        If cell.Value = "" Then
            cell.Value = "No Punch"
        End If
    Next cell

    Else
        'Do nothing
    End If

End Sub

Scott Craner Scott Craner · Accepted Answer · 2020-01-21T15:43:54

You want to loop the criteria range and use Offset()

Sub FinishSheet()
    For Each cell In Worksheets("GerriSheet").Range("A2:A81,H2:H81")
        If cell.Value = "" And cell.Offset(, 4).Value <> "" Then
            cell.Offset(, 4).Value = "No Punch"
        End If
    Next cell
End Sub

If cell in range doesn't equal blank then apply string to cell in another range

1 Answers