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
– GSerg, Commented May 18, 2017 at 19:29

Community · Accepted Answer · 2017-05-23 12:26:26Z

4

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.

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered May 18, 2017 at 19:52

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

That is the answer. Don't use errors for flow control, and of course avoid GOTO jumps and spaghetti logic.

Collectives™ on Stack Overflow

VBA - On error Inside a For Loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related