Combine IsNumeric & CountA in single If statement

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

With the IsNumeric test, it returns "Yes" for a blank cell otherwise works OK.
How to not see blank as IsNumeric?

For Each c In aRng
If IsNumeric(c.Offset(, 3)) Then
MsgBox "Yes"
End If
Next

With the CountA test, it works just fine, returns "8" if Resize range is 'full'.

For Each c In aRng
If WorksheetFunction.CountA(c.Resize(1, 8)) = 8 Then
MsgBox "8"
End If
Next


Then with a corrected IsNumeric test and the CountA test, how to put in a single:

For Each c In aRng
If CountA... and IsNumeric... Then
'Do something
End If
Next

Thanks.
Howard
 
Hi Howard,

Am Sat, 2 Aug 2014 23:01:11 -0700 (PDT) schrieb L. Howard:
With the IsNumeric test, it returns "Yes" for a blank cell otherwise works OK.
How to not see blank as IsNumeric?
Click to expand...

if you only want to check c then:

For Each c In aRng
If Len(c) > 0 And IsNumeric(c) Then
'Do something
End If
Next

If you want to check the resized range also:

For Each c In aRng
If WorksheetFunction.CountA(c.Resize(, 8)) = 8 _
And IsNumeric(c) Then
'Do something
End If
Next


Regards
Claus B.
 
if you only want to check c then:
For Each c In aRng

If Len(c) > 0 And IsNumeric(c) Then

'Do something

End If

Next



If you want to check the resized range also:



For Each c In aRng

If WorksheetFunction.CountA(c.Resize(, 8)) = 8 _

And IsNumeric(c) Then

'Do something

End If

Next

Regards

Claus B.

--
Click to expand...

Hi Claus,

I need to check the Offset(, 3) if it alone is numeric so I added it to your suggestion, plus the resize CountA range needs to equal 8.

Looks like it is spot on and I would swear I tried the same as I have posted here below and it would not compile, trying the "&" and "And", it was giving me fits.


If WorksheetFunction.CountA(c.Resize(, 8)) = 8 _
And IsNumeric(c.Offset(, 3)) Then
'Do something
End If
Next

I'm on track now, Thanks a ton.

Howard
 
Back
Top