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!

 

[Joe4]

Welcome to the Board!

If you just want the value and not a formula (which is what you currently have), try this:

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.Value = Cell.Offset(0,3).Value
Next Cell

 

[Yorke]

Welcome to the Board!

If you just want the value and not a formula (which is what you currently have), try this:

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.Value = Cell.Offset(0,3).Value
Next Cell

This is perfect, thank-you!

 

[Joe4]

You are welcome.

 

[Yorke]

Hi All,

I am now trying to advance the If statement inside the loop to include 'and' & 'or' statements.

The if statement now needs to check if each cell in Column I is empty (blank, or value 0) and whether the cell next to it (left) is not the same value as the cell two to the right - if they are not the same value then use the value from the cell two to the right (a Vlookup formula). This needs to repeat for every row which there is a value in Column A for.

The issue I am having now is the code returns "Type mismatch" error 13.

NB: I've included the Vlookup incase this is causing the error, however the code does works when I only enter one parameter (without And & Or statements).

Here is my code:

Dim Cell As Range
LRS = ThisWorkbook.Worksheets("Status").Range("A" & Rows.Count).End(xlUp).Row 'Endpoint of Column A in Status tab
ThisWorkbook.Worksheets("Status").Range("K2:K" & LRS).FormulaR1C1 = "=VLookup(Status!R[0]C1,'Data'!C2:C8,3,FALSE)"

For Each Cell In ThisWorkbook.Worksheets("Status").Range("I2:I" & LRS)
If (Cell.Value = "" Or Cell.Value = 0) And Cell.Offset(0, -1).Value <> Cell.Offset(0, 2).Value Then Cell.Value = Cell.Offset(0, 2).Value
Next Cell

Thanks!

 

[Joe4]

Are there are any data errors on your sheet?
Error values will often mess with VBA code and/or formulas.

If you hit the "Debug" button when the error pops up, which line of code does it highlight?

You can also add some error handling code to tell you exactly which row of data is causing the error:

Sub MyMacro()

Dim Cell As Range
Dim LRS As Long

LRS = ThisWorkbook.Worksheets("Status").Range("A" & Rows.Count).End(xlUp).Row 'Endpoint of Column A in Status tab
ThisWorkbook.Worksheets("Status").Range("K2:K" & LRS).FormulaR1C1 = "=VLookup(Status!R[0]C1,'Data'!C2:C8,3,FALSE)"

On Error GoTo err_chk
For Each Cell In ThisWorkbook.Worksheets("Status").Range("I2:I" & LRS)
    If (Cell.Value = "" Or Cell.Value = 0) And Cell.Offset(0, -1).Value <> Cell.Offset(0, 2).Value Then Cell.Value = Cell.Offset(0, 2).Value
Next Cell
On Error GoTo 0

Exit Sub

err_chk:
If Err.Number = 13 Then
    MsgBox "Error on row " & Cell.Row
Else
    MsgBox Err.Number & ":" & Err.Description
End If

End Sub

 

[Yorke]

There are error values in other columns, but not in any which this part of the code uses. Even when I remove errors from those cells exactly before the code starts the If Statement, it still fails with 'Type Mismatch'.

Code to remove error cells (which only occur in Columns B to G):
ThisWorkbook.Worksheets("Status").Range("B2:G" & LRS).SpecialCells(xlCellTypeFormulas, xlErrors).Clear

When I Debug it highlights this text:
If (Cell.Value = "" Or Cell.Value = 0) And Cell.Offset(0, -1).Value <> Cell.Offset(0, 2).Value Then

 

[Joe4]

Try running the code I posted in my last post, and it will tell you what data row is causing the error.
Take a look at the row, and check to see what you have in cells H, I, and K of that row.

 

[Yorke]

The code you provided showed that row 5499 has a cell with a N/A value, returned from the Vlookup formula (apologies for not seeing that earlier). There are three rows which return a N/A value. I have checked the Vlookup and the formula appears fine. Even when I change from Exact (False) to Approximate (True) it returns 0 (not the correct value, which is present in the array).

 

[Joe4]

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.