VBA Runtime Error 91 Question

[IvenBach]

I have my code pasted below and would like some help undertsanding why an error now occurs. I use this macro to clear glyphs that creep in when importing and exporting product information. I had the macro working for a while adding to the list of items to check for (partial list posted). I wanted to check for a new character â and copied the Selection.Find portion and added a new On Error GoTo when my error started. I can't figure out why I'm getting the error. I reassigned strREPLACEMENT the line above it and copied the Selection.Find when it was working fine. Anything I need to clarify?

Sub Glyph_Check_and_Removal_or_Proper_Substitution()
Dim strREPLACEMENT As String
strREPLACEMENT = "^^^"

On Error GoTo Err1:
'Section for Character misinterpretation
'Sub ’ (should be "’") for '
'Doesn't come up with error apparently uploading to volusion
    Selection.Replace What:="’", _
        Replacement:="'", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'----------------------------------------
'Right Quotes
    Selection.Replace What:="â�", Replacement:="""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="�", Replacement:="""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
'---------------------------------

    Selection.Replace What:="â¾", Replacement:="¾", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="¾", Replacement:="¾", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'------------
'Non-breaking space replace chr(160) Displays as a <space> but it ISNT!
    Selection.Replace What:=" ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'---------------------------------
    Selection.Replace What:="�", Replacement:=strREPLACEMENT, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'---------------------------------
'Find a way to check for both of these
    Selection.Find(What:=strREPLACEMENT, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select
MsgBox "Careful there buddy! Gotta Find " & strREPLACEMENT & vbCrLf & "After " & strREPLACEMENT & " is corrected reselect and run macro", vbOKOnly, "Your Attention Please!"
    Exit Sub

Err1:
On Error GoTo Err2:
    strREPLACEMENT = "â"
[B]    Selection.Find(What:=strREPLACEMENT, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select
[/B]
MsgBox "Careful there buddy! Gotta Find " & strREPLACEMENT & vbCrLf & "After " & strREPLACEMENT & " is corrected reselect and run macro", vbOKOnly, "Your Attention Please!"
    Exit Sub
Err2:
    MsgBox """Looks"" like everything is clear." & vbCrLf & "Just be careful.", vbOKOnly, "Your Attention Please!"
'---------------------------------

Exit Sub
End Sub

</space>

 

[GlennUK]

You are using the On Error to simply skip execution elsewhere without doing any error handling. Read this:

http://www.cpearson.com/excel/ErrorHandling.htm

... and change your code to this:

finalbit:
On Error GoTo Err2:
    strREPLACEMENT = "â"
    Selection.Find(What:=strREPLACEMENT, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select

MsgBox "Careful there buddy! Gotta Find " & strREPLACEMENT & vbCrLf & "After " & strREPLACEMENT & " is corrected reselect and run macro", vbOKOnly, "Your Attention Please!"
    Exit Sub
Err2:
    MsgBox """Looks"" like everything is clear." & vbCrLf & "Just be careful.", vbOKOnly, "Your Attention Please!"
'---------------------------------

Exit Sub
Err1:
Resume finalbit:
End Sub

 

[IvenBach]

Thanks for getting me pointed in the right direction. I read up on the information about the link you sent me and understood it about half way. After some more searching I came across http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=410 and http://www.fmsinc.com/tpapers/vbacode/debug.asp. I couldn't figure out why the code you gave me would work when mine looked almost identical. After reading up on those 2 sites and having my mind enlightened , I used the Immediate window (ctrl+g) coupled with Debug.Print to show me that the Error number 91 persists in my original code. Your code lets it continue with Resume and works perfectly.

I'm trying to learn more and would like to know why you can't use Err.Clear or Err.Number = 0 and have it continue without coming up with an error (91 in my case) even though I'm trying to trap and handle the error. I know I'm not understanding something and making an assumption here. With Err.Number as 0 shouldn't it come up with the appropriate error and then be put back through to Err2:???

finalbit:
    Debug.Print "Second"
    Debug.Print Err.Number
    On Error GoTo Err2:
        strREPLACEMENT = "â"
        Selection.Find(What:=strREPLACEMENT, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Select
    MsgBox "Careful there buddy! Gotta Find " & strREPLACEMENT & vbCrLf & "After " & strREPLACEMENT & " is corrected reselect and run macro", vbOKOnly, "Your Attention Please!"
        Exit Sub
Err1:
    Debug.Print "First"
    Debug.Print Err.Number
    Err.Clear
    GoTo finalbit:
Err2:
        Debug.Print Err.Number
        MsgBox """Looks"" like everything is clear." & vbCrLf & "Just be careful.", vbOKOnly, "Your Attention Please!"
    Exit Sub

 

[GlennUK]

Just looked at this for 2 seconds, and noticed that you are using Goto finalbit ... instead of Resume finalbit ... a guess would be that you don't realise the significance of using the Resume statement. Can you change it and try it.

 

[IvenBach]

You are correct. Your initial post was working without a flaw. I admit that I have no understanding of the Resume statement. The part I don't understand is why the Resume finalbit: works as opposed to Err.Clear --> GoTo finalbit: I know that I incorrectly assume that by clearing the error or assigning it a value of 0 would allow your code to continue. My question is how/why is Resume different or what does it do differently? Is Resume considered the standard or most appropriate use? Not sure how else to state my question.

 

[GlennUK]

Resume is the statement that exists specifically for resuming execution ... more to read:

http://www.developerfusion.com/article/1741/handling-errors-in-vbvbavbsasp/8/

 

[Norie]

IvenBach

What is Selection when you try and run this code and where do you actually get the error?

Is it on the Find(s) you use at the end?

If it is then there could be a way to avoid the error actually happening, which can sometimes be preferable to using On Error.

PS Are they meant to be Find? All the rest are Replace.

 

[IvenBach]

The last 2 are correctly written when trying to simply find what the strREPLACEMENT variable. I have these as finds because I have had them come up as a variety of possible characters whereas those before it are always the same.

My selection is generally a singular column but may include noncontiguos columns and rows at times which is why I used Selection and is used for the macro in its entirety. Only at the end after the characters that I know how to replace (IE - â¾ and ¾ always become &'frac34; without the ' so it shows up as the 3/4 symbol). I want it to select the characters that have ^^^ or â as these are characters that can differ and I need to determine what they should be. Glenns solution works without fail. I was just wanting to learn why it worked and not my ignorant assumption I posted. Just curiosity killing the cat I guess. I try to not just accept that "it works so it must be fine" understand why it works. Thanks for bearing with me.