VBA - On error Inside a For Loop

Question

I have an OnError method inside the for loop. I am getting an error when the OnError is used for the second time.

This is my code,

For i = lastrow To 2 Step -1
  Sheets(TabName2).Activate
  CombinedKeyVal = Range(CombinedKeyColLet & i).Value

  On Error GoTo Jumpdelete

  Present = WorksheetFunction.Match(CombinedKeyVal, Sheets(TabName1).Columns(6), 0)
  If Present <> "" Then
    GoTo Jumpdontdelete
  End If

Jumpdelete:
  Sheets(TabName2).Activate
  Rows(i & ":" & i).Delete

Jumpdontdelete:

Next

How do I handle this for n number of times. Please kindly share your thoughts.

You are supposed to use `resume` to return from your error handler. E.g. `resume Jumpdontdelete`. — GSerg, May 18 '17 at 19:29

score 4 · Accepted Answer · edited May 23 '17 at 12:26

In this specific case, Dim Present As Variant and use Application.Match instead of WorksheetFunction.Match. The Application.Match function returns an error without raising it (this is why you need to assign as a Variant type):

Dim Present As Variant
For i = lastrow To 2 Step -1
    CombinedKeyVal = Sheets(TabName2).Range(CombinedKeyColLet & i).Value
    Present = Application.Match(CombinedKeyVal, Sheets(TabName1).Columns(6), 0)
    If IsError(Present) Then
        Sheets(TabName2).Rows(i & ":" & i).Delete
    End If
Next

This avoids the spaghettification of your code with tons of GoTo and Resume statements which are tricky to follow & troubleshoot.

Also I cleaned up your code a little bit to avoid relying on Activate.

*That* is the answer. **Don't use errors for flow control**, and of course *avoid GOTO jumps* and spaghetti logic. — Mathieu Guindon, May 18 '17 at 19:56

VBA - On error Inside a For Loop

1 Answers1