"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:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,665
Office Version
365
Platform
Windows
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
35,177
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,665
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,306
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top