Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: "On Error" Statement nested in for loop

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "On Error" Statement nested in for loop

    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 RoryA; Jun 16th, 2015 at 10:39 AM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,117
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    2 Thread(s)

    Default Re: "On Error" Statement nested in for loop

    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
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jun 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "On Error" Statement nested in for loop

    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

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: "On Error" Statement nested in for loop

    Quote Originally Posted by merin95 View Post
    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.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,117
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    2 Thread(s)

    Default Re: "On Error" Statement nested in for loop

    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.
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •