Error message '1004' Simple code cannot get to work 2

[work4livinsean]

Hi, I am a little confused here because my code works the first time the error trap works but the code will not work the second time. I get the error message runtime error 1004. I can type in a number instead of a letter and the error works, but if I do it again it will not work. Basically what I want to do is allow the user to make the same mistake of typing in a number instead of a letter (or vise versa) as many times as he/she does wants to. The reason for this is that I do not want the error message popping up. Can anyone help me with this code or suggest another way of approaching this problem? I would really appreciate it, thanks in advance. Here is my code (basic):

Sub Test()

Dim continue As Boolean
continue = True

Do while continue = True

Dim letCol
letCol = InputBox("Please enter a letter.", "Letter Only")

On Error GoTo AddError
Range(letCol & "12").Select

Dim rowNum
rowNum = InputBox("Please enter a number.", , "Number Only")

On Error GoTo AddError
Range("A" & rowNum).Select

'Code to be executed here

AddError:
Range("A1").Select
Set letCol = Nothing
Set rowNum = Nothing


continue = MsgBox("Would you like to run this macro again?", vbYesNo, "Retry")
If continue = vbYes Then
continue = True
Else
continue = False
End If
Loop

End Sub


Thanks in advance!

 

[xlbo]

'continue' is defined as boolean. vbYES is not boolean. vbyes does not = false as there are other options and other numbers that can be returned. You need to use a different variable

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[work4livinsean]

Okay, I just made continue as so-

Dim continue

And this works for the loop. I am still getting the run-time error message though I do not understand why if the AddError works the first time and you make the same mistake the AddError does not work the second time. For example, if I ran this macro and typed in '3', and then the continue msgbox comes up. I hit enter and type '3' again I then get an error (debug) message!!! Why does this happen? How can I fix this?

Thanks.

 

[rmikesmith]

A couple of points:

You do not have the error handler set up in a normal fashion; i.e. an error handler is not positioned inside a loop. That's part of your problem. Related is the fact that your "error handler" segment will always be executed, not what you want to happen.

I would not use classic error trapping to handle user input errors. Your code should anticipate those and respond appropriately with generating an actual run-time error.

It looks like you're asking the user to enter a cell's column designation (alpha characters) and row designation (a numeric value). Is this correct?


Regards,
Mike

 

[TonyJollans]

I suggest you read up the Help on error handling.

After you have had an error, you are never Resume-ing normal execution mode; you are staying in error-handling mode so, when another error occurs, VBA traps it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[rmikesmith]

Correction:

Your code should anticipate those and respond appropriately without generating an actual run-time error.

Good catch Tony. You've explained the root cause, I believe.

Regards,
Mike

 

[work4livinsean]

Okay, first off thank you for responding! In response to Mike, yes I do want the user to enter a column and row letter/number. I guess my brain was not functioning correctly, I looked up help for the error handling. You both were right about where I placed the error-handler. It needs to be outside the loop and it will enter the loop now after the error is handeled. Sorry I did not let you know that I do have the error handler do something. I just didn't put it in for simplicity. My code now looks like this and works great, Thanks!!!!


Sub Test()

Dim continue
continue = True

Do While continue = True

Dim letCol
letCol = InputBox("Please enter a letter.", "Letter Only")

On Error GoTo AddError
Range(letCol & "12").Select

Dim rowNum
rowNum = InputBox("Please enter a number.", , "Number Only")

On Error GoTo AddError
Range("A" & rowNum).Select

'Code to be executed here

Resume_Here:

continue = MsgBox("Would you like to run this macro again?", vbYesNo, "Retry")
If continue = vbYes Then
continue = True
Else
continue = False
End If
Loop

Exit Sub

AddError:
Range("A1").Select
Resume Resume_Here

End Sub


Thank you,
Sean

 

[rmikesmith]

Tony, correct me if I'm wrong, but jumping into a loop from an error handler is bad practice. I would again suggest not using an error handler to respond to user input inaccuracies.


Regards,
Mike

 

[work4livinsean]

Alright, I will play around with it. Right now I am not experiencing any problems. I understand your concern because variables are getting reset and/or reinitializing. If I do find a error I will post it. I will try working with the code another way as well. Thanks,

Sean

 

[TonyJollans]

Yes Mike, I agree. Jumping into a loop is never a good idea - from an error handler or anywhere else. It relies on undocumented compiler behaviour at best.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[