On Error GoTo is causing data to be looped over twice

[brewitbold]

Hello!


This is my first time using an On Error GoTo solution within a macro and I cannot find any documentation online to help me with an issue I am running in to. I think it's an issue of where in the loop I have placed the error handling line.

The snippet of code below performs a lookup and stores either a 1 or a 2 in a variable called "State." If the lookup value does not exist on the second sheet, then then "State" should be "Not Found. Currently what is happening is that a record that is NOT on the second sheet initially returns "Not Found" (yay!) and then the code loops back up to the if statement and overwrites the result to "2."

My thought was that the error handling would simply produce "not found" and then move to the next row.

I have commented out the actual vlookup, as it is long and does not seem to be impacting the actual error (I've tested it and it returnes accurate values 100%)

For i = 2 To lastRow
    lookupValue= 'Returns the value of a Vlookup from another sheet.

On Error GoTo EmpNotFound:
     If lookupValue= "string1" Then
     state = "1"
     
     Else
     state = "2"

End If

Next




EmpNotFound:
    state = "Not Found"
    Resume Next

Thank you for the assistance and collective brain power that this site attracts!

 

[Joe4]

Is the issue that your lookup cannot find anything, so it is returning an error?
If that is the case, then use IFERROR with your VLOOKUP to return something other than en error.
See: https://exceljet.net/formula/vlookup-without-na-error

 

[JoeMo]

If the lookup value can't be found then your error handler sets state to a string (OK so far), but the resume next then sends that string back to the If-Then construct and since the string "Not Found" is not equal to "String1" the Else line changes state to a 2.

Maybe easier to do something like this:

For i = 2 To lastRow
    lookupValue= 'Returns the value of a Vlookup from another sheet.
    If IsError(lookupValue) Then GoTo NX
     If lookupValue = "string1" Then
     State = "1"
     
     Else
     State = "2"

End If
NX:
Next

'rest of code

 

[brewitbold]

I implemented this code fix and I now get "run-time error 1004: Unable to get the vlookup property of the worksheetfunction class" on the vlookup line.

 

[JoeMo]

I implemented this code fix and I now get "run-time error 1004: Unable to get the vlookup property of the worksheetfunction class" on the vlookup line.

Forgot a line - try this:

For i = 2 To lastRow
       On Error Resume Next
    lookupValue= 'Returns the value of a Vlookup from another sheet.
    If IsError(lookupValue) Then
       State = "Not found"
       GoTo NX
       End If
     If lookupValue = "string1" Then
     State = "1"
     
     Else
     State = "2"

End If
NX: On Error GoTo 0
Next

'rest of codeEnd Sub