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

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

HTH. Best wishes Harald

"scott" skrev i melding
...
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

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


"Harald Staff" wrote in message
...

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

HTH. Best wishes Harald

"scott" skrev i melding
...
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

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.

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
It now checks the cells, but IsNumeric() returns True for empty cells, why
does that happen?


"Harald Staff" wrote in message
...

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

HTH. Best wishes Harald

"scott" skrev i melding
...
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

On Tue, 8 Feb 2005 11:14:43 -0600, "scott" wrote:

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

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.

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
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