What is Wrong With Here

[yasirsaeed2002]

Hi Guys,

I am inexperienced in VBA. I have written the following code. The program runs once if the Name is not found but returns error 91 If I retry rather than going to Namecorrection Label. I wonder why?

Thanks in Advance
Yasir


Dim Name as String

RetryAgain:

Name = Inputbox("Type in Your Name Here")

On Error GoTo NameCorrection

Range("B1", Range("B1").End(xlDown)).Find(name).Select (Error 91 here the 2nd time)

On Error GoTo 0

NameCorrection:

MBox = MsgBox("The Name You Entered was Not Found. Do you Want To Try Again", vbRetryCancel)

If MBox = vbRetry Then GoTo RetryAgain

 

[mrhstn]

Hi

You probably don't need to "goto" anything, try

Sub Yasir()
Dim DoAgain As Variant
Dim findName As String
Dim lstRw As Integer
Dim found As Range
Dim msg As String


lstRw = Cells(Rows.Count, 2).End(xlUp).Row


Do Until DoAgain = vbNo
    findName = InputBox("Type in Your Name Here")
    
    Set found = Range("B1:B" & lstRw).Find(findName)
    
    If Not found Is Nothing Then
        msg = "I found " & findName & " at " & found.Address
    Else
        msg = "The Name You Entered was Not Found"
    End If
    
    DoAgain = MsgBox(msg & ", go again?", vbYesNo)
Loop
End Sub

 

[yasirsaeed2002]

Hi

You probably don't need to "goto" anything, try

Sub Yasir()
Dim DoAgain As Variant
Dim findName As String
Dim lstRw As Integer
Dim found As Range
Dim msg As String


lstRw = Cells(Rows.Count, 2).End(xlUp).Row


Do Until DoAgain = vbNo
    findName = InputBox("Type in Your Name Here")
    
    Set found = Range("B1:B" & lstRw).Find(findName)
    
    If Not found Is Nothing Then
        msg = "I found " & findName & " at " & found.Address
    Else
        msg = "The Name You Entered was Not Found"
    End If
    
    DoAgain = MsgBox(msg & ", go again?", vbYesNo)
Loop
End Sub

Hi Mrhstn,

Thanks for your reply. The code work perfect except when you press cancel it does not exit sub rather gives a message as follows:

I found at $B$14, go again?

Also please I would appreciate if you can comment on the problem with my code as I was doing some practice.

Kind Regards
Yasir

 

[mrhstn]

Sorry, forgot about cancelling

Try

findName = InputBox("Type in Your Name Here")   
If findName = vbNullString Then Exit Do

in place of

    findName = InputBox("Type in Your Name Here")

 

[yasirsaeed2002]

Sorry, forgot about cancelling

Try

findName = InputBox("Type in Your Name Here")   
If findName = vbNullString Then Exit Do

in place of

    findName = InputBox("Type in Your Name Here")

Perfect. Thanks.

 

[Fluff]

Also please I would appreciate if you can comment on the problem with my code as I was doing some practice.

The problem is with your error handler. VBA can only handle one error at a time, so if you get an error, it needs to be cleared before any other errors can be handled. have a look here for more info.

 

[yasirsaeed2002]

The problem is with your error handler. VBA can only handle one error at a time, so if you get an error, it needs to be cleared before any other errors can be handled. have a look here for more info.

Thanks Fluff. Really Helpful.

Regards

 

[Fluff]

You're welcome