Problem: error on numeric

[willz99ta]

Howdy,

Let me thank you before-hand for any help you can give me.

I wrote a program that uses two long lists of DBL numeric values. The
problem is that sometimes the information I paste into my program is non-
numeric (When they give me bad data). When ever I paste a value with even 1
non-numeric character somewhere in it, it blows up with the Type Mismatch
error.

What I need to do is come up with some basic error handling code, but hell if
I know what works.

I am using this code to take off the first two letters if they are alphabetic:

-------------------------------------------------------------
Do Until IsEmpty(ActiveCell)
ActiveCell.NumberFormat = "@"
If Mid(ActiveCell.Value, 1, 1) Like "[A-Za-z]" And Mid(ActiveCell.Value,
2, 1) Like "[A-Za-z]" Then
ActiveCell.Value = (Mid(ActiveCell.Value, 3, Len(ActiveCell.Value) -
2))
End If

<--------------------------------Maybe I can put the code
here?

ActiveCell.Offset(1, 0).Select
Loop
-------------------------------------------------------------

What is the best way to scan through the selected cell for any character that
is not a number or a space and then stop the program and pop up a message box
with a message about the error?

Thanks again for any help,
William

 

[Ken Hudson]

Hi William,
When you loop through a worksheet, it is faster if you don't "cell offset"
in the loop.
If you are testing for entries in column A, you can count the rows with data
in column A and then loop through each one "virtually."
Perhaps the code below will work for you.

NumRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To NumRows
If Not IsNumeric(Cells(Iloop, "A")) Then
Ans = MsgBox("Cell 'A" & Iloop & "' does not appear to be a
number.",_ vbOKOnly)
End If
Next Iloop