IsNumeric Test

[scott]

I'm trying to test the cells A2 to C2 to see if any cells do not contain
numerical values. Can someone help me with below code? I'm getting an error
saying "sub or function not defined." I am running it from an add-in and may
need it to set "sheet1" as the target spreadsheet, but didn't know syntax.

For Each cell In Me.Range("A2:C2")

Select Case IsNumeric(cell.Value)
Case True
MsgBox "a1 numeric"
Case False
MsgBox "a1 non numeric"
Case Else
MsgBox "Don't know"
End Select
Next

 

[Harald Staff]

For Each cell In ActiveWorkbook.Sheets("sheet1").Range("A2:C2")

HTH. Best wishes Harald

 

[scott]

It now checks the cells, but IsNumeric() returns True for empty cells, why
does that happen?

 

[Tom Ogilvy]

I placed this in a sheet module as the click event for a command button and
it worked fine:

Private Sub CommandButton1_Click()
For Each cell In Me.Range("A2:C2")

Select Case IsNumeric(cell.Value)
Case True
MsgBox "a1 numeric"
Case False
MsgBox "a1 non numeric"
Case Else
MsgBox "Don't know"
End Select
Next


End Sub


If it isn't in a sheet module, then the use of ME would be meaningless. In
a sheet module, it refers to the sheet containing the code.

 

[Tom Ogilvy]

isnumeric tests if the value can be evaluated as a number. A blank cell can
be evaluated as zero, so it is true.

Just check if it is empty before checking if it is numeric.

 

[Ron Rosenfeld]

It now checks the cells, but IsNumeric() returns True for empty cells, why
does that happen?

Because a blank can be evaluated as a number.

If you want to look for a number being present, as opposed to contents which
can be evaluated as a number, try:

application.WorksheetFunction.IsNumber


--ron