CODEWITHSUNDEEP
excelvba
CustomX
CustomX

Reputation: 10113

How to automatically enter a value on error instead of skipping?

I have the following code, which works as long as Gi is found in the Articles sheet. If Gi isn't found I receive an error. I can skip the error using On Error Resume Next, but I want to be able to automatically enter a value here. Any suggestions on how? I have found documentation to go somewhere on an error, but that will stop the FOR loop.

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
    Range("N" & i).Value = Application.WorksheetFunction.VLookup(Range("G" & i), Sheets("Articles").Range("A2:B5000"), 2, False)
Next i

Upvotes: 1

Views: 37

Answers (1)

Tode
Tode

Reputation: 12060

In that case you can use an on error goto statement together with a resume. In the example code I just catch an error in ONE line with that resume, all other errors are handled by a msgbox.

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
    On Error Goto ErrorNext
    lookupValue = Application.WorksheetFunction.VLookup(Range("G" & i), _
      Sheets("Articles").Range("A2:B5000"), 2, False)
    On Error Goto ErrorHandler
ResumeHere:
    Range("N" & i).Value = lookupValue
Next i

'-------- All of your other code goes here  ------------

Exit Sub
ErrorNext:
    lookupValue = "Some default value"
    Resume ResumeHere
ErrorHandler:
    MsgBox "Another Error occured: " & error

If you don't want a "default" error- handler, then replace On Error goto ErrorHandler with On Error Goto 0. Otherwise you will create a wonderfull infinit loop if there is an error just after your For- Loop...

So without an error- handler except for that one line the code would look like this:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
    On Error Goto ErrorNext
    lookupValue = Application.WorksheetFunction.VLookup(Range("G" & i), _
      Sheets("Articles").Range("A2:B5000"), 2, False)
    On Error Goto 0
ResumeHere:
    Range("N" & i).Value = lookupValue
Next i

'-------- All of your other code goes here  ------------

Exit Sub
ErrorNext:
    lookupValue = "Some default value"
    Resume ResumeHere

Basically code without an error handler is always bad. All code that I personally write looks like this:

  On Error Goto ErrorHandler
'----------------------------------------

' Here is the real code

'----------------------------------------
EndSub:
  Exit Sub
ErrorHandler:
  '- Do my error handling here, at least show a "better" message
  MsgBox "An error occured: " & Error & " in line " & Erl
  Resume EndSub

My "real" code just comes between the two lines... And this "real" code of course can contain handling a "special" error, that just needs a resume to another position as in the examples above.

Upvotes: 2

Related Questions