If Statement in loop

[Yorke]

Hi All,

I am trying to write a simple If statement inside a loop which applies to all rows which have a value in Column A.

The if statement just needs to check if each cell in Column H is empty and if so paste the value from the same row in Column K. This needs to repeat for every row which there is a value in Column A for.

Here is my code:

LRS = ThisWorkbook.Worksheets("Status").Range("A" & Rows.Count).End(xlUp).Row
Dim Cell As Range

For Each Cell In Range("H2:H" & LRS)
If Cell.Value = "" Then
Cell.FormulaR1C1 = "=RC11"
End If
Next Cell

Thanks in advance, I'm a VBA noob and I've been searching all over for solutions!

 

[Yorke]

Which column returns the #N/A?
I don't think you can compare anything to the #N/A error.

I would recommend wrapping your VLOOKUP formula in a IFNA function to return something other than an error when no match is found (see: IFNA function).

So, that structure would look something like:

=IFNA(VLOOKUP(...),what to return if no match)

See if making that changes allows your code to run without error.

This works perfectly now, thank-you again! Your replies really help not only solve but learn!

Should I set your latest reply to solution, or leave as the first?

 

[Joe4]

You are welcome.

Should I set your latest reply to solution, or leave as the first?

Just leave the first, as that is the one that answered the original question.