"On Error" Statement nested in for loop

merin95

New Member
Joined
Jun 16, 2015
Messages
2
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:
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!
 
Last edited by a moderator:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,279
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.
Code:
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

New Member
Joined
Jun 16, 2015
Messages
2
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,279
Oops, forgot to remove the = "MATCH" bit.
Code:
    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.
Code:
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.
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top