"On Error" Statement nested in for loop

[merin95]

Hello,

I'm new to VBA Programming, but my first program may be a little more complicated than I can handle. Here is a segment from my code:

On Error GoTo Error_Handler
For i = 2 To top
    With Range("I" & i + 57)
        .Formula = "=VLOOKUP(H" & i + 57 & ",F$59:G$" & top + 57 & ",2, FALSE)"
        .Font.Name = "Arial"
        .Font.Size = 8
    End With
    
    If Range("I" & i + 57) = "MATCH" Then
        Range("H" & i + 57).Interior.ColorIndex = 7
        GoTo Loop_Resume
    End If
Loop_Resume:
Next i

Error_Handler:
    Err.Clear
    On Error GoTo Error_Handler
    GoTo Loop_Resume

The VLOOKUP function will determine if the value in a cell matches a cell in a different data collection. If it is a match, the cell next to it will have the value "MATCH", but if it doesn't, the result comes out to be "#N/A". I want to be able to highlight the matched cells in pink, and I want to go about doing this with an error handler. So far, I've got the loop going through the "MATCH"'s and the first error just fine, but as soon as it gets to the second error, it gives me the runtime error "type mismatch".

I've tried several variations of this code and this is the most luck I've had. Thanks in advance to anyone who offers a solution!

 

[Norie]

What's the formula in column I?

You could change that formula so it doesn't return an error value and then you wouldn't need the error handler.

Or you could check to see if the formula is returning an error, something like this.

For i = 2 To top
    With Range("I" & i + 57)
        .Formula = "=VLOOKUP(H" & i + 57 & ",F$59:G$" & top + 57 & ",2, FALSE)"
        .Font.Name = "Arial"
        .Font.Size = 8
    End With

    If Not IsError(Range("I" & i + 57)) = "MATCH" Then
        Range("H" & i + 57).Interior.ColorIndex = 7
    End If
Next i

 

[merin95]

The formula in column I is the VLOOKUP which will see if the compared values match. I don't really know enough about the language to know a formula that will do the same thing but return a value if there is no match.

I used your code, but I'm still getting a runtime error at
If Not IsError(Range("I" & i + 57)) = "MATCH" Then

I feel like I was close with the first code, but for some reason, if it encountered one error, it wouldn't know how to act if it came across a second error

 

[RoryA]

I feel like I was close with the first code, but for some reason, if it encountered one error, it wouldn't know how to act if it came across a second error

Have a read of this: On Error WTF? | Excel Matters

Essentially you needed to use Resume rather than Goto when returning to the label.

 

[Norie]

Oops, forgot to remove the = "MATCH" bit.

    If Not IsError(Range("I" & i + 57)) = "MATCH" Then
        Range("H" & i + 57).Interior.ColorIndex = 7
    End If

If you really want to use On Error... then I would suggest something like this.

For i = 2 To top
    With Range("I" & i + 57)
        .Formula = "=VLOOKUP(H" & i + 57 & ",F$59:G$" & top + 57 & ",2, FALSE)"
        .Font.Name = "Arial"
        .Font.Size = 8
    End With

    On Error Resume Next

    If Range("I" & i + 57) = "MATCH" Then
        Range("H" & i + 57).Interior.ColorIndex = 7
    End If

    On Error Resume 0

Next i

That puts the error handling at the point in the code where the error is going to happen, it also avoids jumping out the loop and then jumping back - that kind of thing can make code hard to follow.