CODEWITHSUNDEEP
vbaexcel
sagar
sagar

Reputation: 397

How to use IfError GoTo Next function

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

Upvotes: 1

Views: 302

Answers (3)

Ralph
Ralph

Reputation: 9434

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

Upvotes: 1

trincot
trincot

Reputation: 350147

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.

Upvotes: 1

user7857211
user7857211

Reputation:

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!

Upvotes: 0

Related Questions