How to use IfError GoTo Next function

Question

Please guide me how can I directly resume to "s = s + 1" if i am getting error in the sprd = Application.Find(",", xword). Please guide.

For xx = 2 To 15494
xword = Cells(s, m)

If xword <> "" Then
le = Len(xword)
sprd = Application.Find(",", xword)'' If I am getting Error
old_sprd = sprd
's = 1
Star = 1
Do While sprd <> 0

word = Mid(xword, Star, sprd - 1)
xword = Mid(xword, sprd + 1, le)
s = s + 1
Rows(s).Insert
Cells(s, m) = word
sprd = Application.Find(",", xword)
If IsError(sprd) Then sprd = 0
If sprd = 0 Then
s = s + 1
Rows(s).Insert
Cells(s, m) = xword
End If
le = Len(xword)

Loop
End If

s = s + 1 '' My Code supposed to directing divert in This line.
Next

You loop through xx, but there is no use of xx in your code? — Luuklag, Apr 26 '17 at 11:25
Why don't you use `Instr` instead of `Application.Find`? It will return 0 if the search string isn't found, rather than an error value. — Rory, Apr 26 '17 at 11:33

score 1 · Answer 1 · edited May 23 '17 at 11:46

The following code answers your question as asked:

For xx = 2 To 15494
    xword = Cells(s, m)

    If xword <> "" Then
    le = Len(xword)
    On Error GoTo NextLine
    sprd = Application.Find(",", xword) '' If I am getting Error
    On Error GoTo 0
    old_sprd = sprd
    's = 1
    Star = 1
    Do While sprd <> 0

    word = Mid(xword, Star, sprd - 1)
    xword = Mid(xword, sprd + 1, le)
    s = s + 1
    Rows(s).Insert
    Cells(s, m) = word
    sprd = Application.Find(",", xword)
    If IsError(sprd) Then sprd = 0
    If sprd = 0 Then
    s = s + 1
    Rows(s).Insert
    Cells(s, m) = xword
    End If
    le = Len(xword)

    Loop
    End If
NextLine:
    s = s + 1 '' My Code supposed to directing divert in This line.
Next

Basically, there are three changes: (1) just before issuing the Application.Find command there is a line telling VBA what to do in case of an error --> it should go to NextLine. NewLine is like a bookmark and can be any name you want. You merely need to tell VBA where this bookmark is. That's the second change in your code: (2) adding a line just before s = s + 1 telling VBA that this is the "bookmark" called NewLine. The third change is to tell VBA to only use this "bookmark" if the error occurs on the line Application.Find. In all other cases VBA should just pass the error back to you (the user). So, (3) directly after the line Application.Find the error trapping is being turned off again.

Yet, a better solution would be to use InStr() like so:

For xx = 2 To 15494
    xword = Cells(s, m)
    If xword <> "" Then
        le = Len(xword)
        If InStr(1, xword, ",", vbTextCompare) Then
            sprd = Application.Find(",", xword)
            old_sprd = sprd
            Star = 1
            Do While sprd <> 0
                word = Mid(xword, Star, sprd - 1)
                xword = Mid(xword, sprd + 1, le)
                s = s + 1
                Rows(s).Insert
                Cells(s, m) = word
                sprd = Application.Find(",", xword)
                If IsError(sprd) Then sprd = 0
                If sprd = 0 Then
                    s = s + 1
                    Rows(s).Insert
                    Cells(s, m) = xword
                End If
                le = Len(xword)
            Loop
        End If
    End If
    s = s + 1 '' My Code supposed to directing divert in This line.
Next xx

score 1 · Accepted Answer · answered Apr 26 '17 at 11:32

1

Instead of Application.Find use InStr (and swap the arguments):

sprd = InStr(xword, ",")

That does not produce an error, and is more efficient.

In case no match is found, sprd will be zero, and so your Do While loop will be skipped.

answered Apr 26 '17 at 11:32

trincot

317,000
35
244
286

score 0 · Answer 3 · edited May 08 '18 at 21:49

0

Something like this?

On Error Goto JumpHere:
i = 1 / 0 'throws error
i = 2 'this line is never executed
JumpHere:
i = 1 'code resumes here after error in line 2

Hope this helps!

edited May 08 '18 at 21:49

Community

1
1

answered Apr 26 '17 at 11:29

How to use IfError GoTo Next function

3 Answers3