Combine IsNumeric & CountA in single If statement
 

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

Combine IsNumeric & CountA in single If statement
 

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?

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Combine IsNumeric & CountA in single If statement
 

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.

--

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