error handling...I'm stumped!

[chouston]

Can anyone tell me why this isn't working?
If there is an invalid unit 3 entered it works, but if there is a valid unit # entered it still prints the error message ("Please enter valid...").


'prompt for unit number
unit = InputBox("Enter Unit #:", "Final Billing Form")

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find(unit, lookat:=xlWhole).Row
If resrow Is Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
End If
End With

[brettvba]

On 2002-05-02 13:43, chouston wrote:
Can anyone tell me why this isn't working?
If there is an invalid unit 3 entered it works, but if there is a valid unit # entered it still prints the error message ("Please enter valid...").


'prompt for unit number
unit = InputBox("Enter Unit #:", "Final Billing Form")

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find(unit, lookat:=xlWhole).Row
If resrow Is Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
End If
End With

your inputbox needs to be set up for numbers the default is a string

use
unit = (CLng(InputBox("Enter Unit #:", "Final Billing Form")))

instead and it will recongnise the numbers as before the number when entered would be recognised as "3" instead of 3 the Clng just dims the inputbox as long. If this is not the case post all of your code.

[chouston]

On 2002-05-02 13:58, brettvba wrote:

your inputbox needs to be set up for numbers the default is a string

use
unit = (CLng(InputBox("Enter Unit #:", "Final Billing Form")))

instead and it will recongnise the numbers as before the number when entered would be recognised as "3" instead of 3 the Clng just dims the inputbox as long. If this is not the case post all of your code.

I'm pretty sure Clng won't work b/c many units have letters such as "101A", "B102", or don't have letters but have non-integer name like "101-2" or "277-1". I tried dim the inputbox as varient and string with your method but it still didn't work. I'm very new to VBA and I kinda guessed on the whole "on error resume next" thing. Here's some more code:

Sub finalbill()

Dim unit As String
Dim OutDate As Date



Application.ScreenUpdating = False


'This starts the final bill procedure

'prompt for unit number
'unit = InputBox("Enter Unit #:", "Final Billing Form")
unit = (CLng(InputBox("Enter Unit #:", "Final Billing Form")))

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find(unit, lookat:=xlWhole).Row
If resrow Is Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
End If
End With


-- If I take out the "On Error..." and the "It resrow is empty..." the macro works unless an invalid unit is entered (i.e. one not found in B43:B422 range).

BTW thanks for your prompt reply Brettvba!

[brettvba]

unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))
would be a variant inputbox

and just dim unit as dim unit

[ This Message was edited by: brettvba on 2002-05-02 14:22 ]

[brettvba]

try this it works

Dim unit
Dim OutDate As Date



Application.ScreenUpdating = False


'This starts the final bill procedure

'prompt for unit number
'unit = InputBox("Enter Unit #:", "Final Billing Form")
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find((unit), lookat:=xlWhole).Row
If resrow = Empty Then MsgBox ("Please enter a valid unit number.")
finalbill

End With
End Sub

Sub finalbill()

Dim unit
Dim OutDate As Date



Application.ScreenUpdating = False


'This starts the final bill procedure

'prompt for unit number
'unit = InputBox("Enter Unit #:", "Final Billing Form")
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find((unit), lookat:=xlWhole).Row
If resrow = Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
Else
End If
End With
End Sub


[ This Message was edited by: brettvba on 2002-05-02 14:37 ]

[chouston]

On 2002-05-02 14:19, brettvba wrote:
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))
would be a variant inputbox

and just dim unit as dim unit

[ This Message was edited by: brettvba on 2002-05-02 14:22 ]

Here's with the recommendations you've given me:

Sub finalbill()

Dim unit
Dim OutDate As Date



Application.ScreenUpdating = False


'This starts the final bill procedure

'prompt for unit number
'unit = InputBox("Enter Unit #:", "Final Billing Form")
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find(unit, lookat:=xlWhole).Row
If resrow Is Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
End If
End With

this works great when an invalid unit is entered. msgbox "Please enter a valid ..."
pops up. However even when a valid unit number is entered the same msgbox pops up.

However, before I started trying to handle the errors this worked for valid unit, but not for invalid units:

Sub finalbill()

Dim unit
Dim OutDate As Date



Application.ScreenUpdating = False


'This starts the final bill procedure

'prompt for unit number
'unit = InputBox("Enter Unit #:", "Final Billing Form")
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))

'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
resrow = .Find(unit, lookat:=xlWhole).Row
End With

Thanks again for your help Brettvba

[chouston]

oops, you replied before my last reply, let me try your solution before you respond to my last one.

Thanks!

[brettvba]

use the second example

[chouston]

Thank so much for your help, brettvba, I finally worked out the bugs!

[brettvba]

you would probably wont to enter
Sub finalbill()

Dim unit
Dim OutDate As Date
Application.ScreenUpdating = False
unit = (CVar(InputBox("Enter Unit #:", "Final Billing Form")))
If unit = "" Then Exit Sub
'find row for that unit number
With Sheets("Ridge Download").Range("B43:B422")
On Error Resume Next
resrow = .Find((unit), lookat:=xlWhole).Row
If resrow = Empty Then
MsgBox ("Please enter a valid unit number.")
finalbill
Else
End If
End With
End Sub

that extra line under the input box to exit the loop aswell otherwise it will keep going if they try to cancel