Find sheet macro help

[Firecop1348]

I have a macro that will find a sheet in my workbook. It works great the first time it runs , If I put in a name of a sheet that is not in my workbook it sayes "no record found for (fName) try again" I hit try again and it works but if it doesent find the sheet name the second time I get a Run time error '9' subsript out of range. I do not understand why it will work fine the first time but not the second time. I need it to go back into the "No record found for (fname) try again" so I can keep trying until I find the sheet name.

Sub FindName()
here:
On Error GoTo errhandler
fname = InputBox("please enter the " & _
"name to find.", "Search For")
ActiveWorkbook.Sheets(fname).Activate
Exit Sub
errhandler:
noFind = MsgBox("No record found for " & _
UCase(fname), vbYesNo, "Try Again")
Select Case noFind
Case Is = vbYes
GoTo here
Case Is = vbNo
End Select
End Sub

 

[Glory]

You need to empty the variable before the second run-through.

Public Sub FindName2()
Dim fname As String
Dim Toggle As Boolean
here:
fname = InputBox("Please enter the " & _
        "name to find.", "Search For")
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name = fname Then
        ActiveWorkbook.Sheets(fname).Activate
        Toggle = True
    End If
Next
If Not Toggle Then
    NoFind = MsgBox("Try Again?", _
    vbYesNo, "No record found for " & UCase(fname))
End If
If NoFind = vbYes Then
    [COLOR=red][B]fname = Empty
[/B][/COLOR]    GoTo here
End If
End Sub

 

[AlphaFrog]

Or try something like this...

Sub FindName2()

    Dim fname As String, ws As Worksheet

    Do
        fname = InputBox("please enter the " & _
                "name to find.", "Search For")
        If fname = vbNullString Then Exit Sub 'User canceled
        
        On Error Resume Next
            Set ws = ActiveWorkbook.Sheets(fname)
        On Error GoTo 0
        
        If Not ws Is Nothing Then
            ws.Activate
        ElseIf MsgBox("No record found for " & _
                       UCase(fname), vbYesNo, _
                       "Try Again") = vbNo Then Exit Do
        End If
    
    Loop While ws Is Nothing

End Sub

 

[Firecop1348]

Thanks both of you, it works great now. Could one of you tell me what was wrong with mine? Im trying to learn how to do this stuff, but have to know were my mistakes are so I dont make them again.
Thanks again.

 

[Glory]

What I said before was wrong. You actually don't need to empty the variable before restarting the loop, and it doesn't help to do that.

Edit: I might have encountered something similar to this while working on this thread... See MSDN on "array is fixed or temporarily locked".

I think the GoTo statement might be is cutting off some part of the On Error operation? That's my best guess.

It looks like the macro still thinks it's working out the On Error statement when you send it back up to "here". It's encountering the same error both times (MsgBox Err.Number shows "9", subscript out of range). Only, the second time, the error handler isn't triggered.

 

[AlphaFrog]

Could one of you tell me what was wrong with mine?

Using your original code, change the GoTo to Resume. The Resume statement instructs VBA to resume execution at a specified point in the code. You can use Resume only in an error handling block. Any other use will cause an error.

Sub FindName()
here:
On Error GoTo errhandler
fname = InputBox("please enter the " & _
"name to find.", "Search For")
ActiveWorkbook.Sheets(fname).Activate
Exit Sub
errhandler:
noFind = MsgBox("No record found for " & _
UCase(fname), vbYesNo, "Try Again")
Select Case noFind
Case Is = vbYes
[COLOR="Red"]Resume[/COLOR] here
Case Is = vbNo
End Select
End Sub

When the first error is raised, execution transfers to the errhandler:. On Error wont trap subsequent errors until the Resume statement is invoked. Resume is the only way, aside from exiting the procedure, to get out of an error handling block. Do not use the GoTo statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers.

I think this site explains it well http://www.cpearson.com/excel/errorhandling.htm
Read the two sections:

• Error Handling Blocks And On Error Goto
• The Resume Statement