I'm trying to determine how many numeric values are contained within a
range filled with many blanks. I've tried both the ISNUMERIC and the
ISBLANK code, the first just counts all the cells even the blanks, and
the second gives me an error when i run the program

Here is the code:
For C = Row To value
NotBlank = IsNumeric(range(f_column & C))
If NotBlank = True Then
NoBlanks = NoBlanks + 1
End If
Next C

Any ideas????
Thanks in advance,

dani


--
Dani
------------------------------------------------------------------------
Dani's Profile: http://www.excelforum.com/member.php...o&userid=23542
View this thread: http://www.excelforum.com/showthread...hreadid=383153

Hi,

Try

NotBlank = Application.IsNumber(range(f_column & C))

HTH

"Dani" wrote:


I'm trying to determine how many numeric values are contained within a
range filled with many blanks. I've tried both the ISNUMERIC and the
ISBLANK code, the first just counts all the cells even the blanks, and
the second gives me an error when i run the program

Here is the code:
For C = Row To value
NotBlank = IsNumeric(range(f_column & C))
If NotBlank = True Then
NoBlanks = NoBlanks + 1
End If
Next C

Any ideas????
Thanks in advance,

dani


--
Dani
------------------------------------------------------------------------
Dani's Profile: http://www.excelforum.com/member.php...o&userid=23542
View this thread: http://www.excelforum.com/showthread...hreadid=383153

I tried it but it still doesn't work....

is there any other ideas????


-dani


--
Dani
------------------------------------------------------------------------
Dani's Profile: http://www.excelforum.com/member.php...o&userid=23542
View this thread: http://www.excelforum.com/showthread...hreadid=383153

Hi Dani,

Try:

'====================
Sub Tester()

Dim rng As Range
Dim iCnt As Long

Set rng = Range("A1:D20") '<<==== CHANGE
On Error Resume Next
iCnt = Intersect(Selection, rng. _
SpecialCells(xlCellTypeConstants, xlNumbers)). _
Cells.Count
On Error GoTo 0
MsgBox iCnt
End Sub
'<====================

or:

'====================
Sub Tester2()

Dim rng As Range, rcell As Range
Dim iCnt As Long

Set rng = Range("A1:D20") '<<==== CHANGE

For Each rcell In rng
If Not IsEmpty(rcell) Then
If IsNumeric(rcell) Then iCnt = iCnt + 1
End If
Next

MsgBox iCnt
End Sub
'<====================

The first method will fail if the range comprises more than 8192 contiguous
areas but may be faster. The error handler is used to catch the error
produced if the SpecialCells method is unable to find a cell.

The second method should work in all likely scenarios and, unless the range
area is very large, should have little discernable speed overhead.

---
Regards,
Norman



"Dani" wrote in message
...

I'm trying to determine how many numeric values are contained within a
range filled with many blanks. I've tried both the ISNUMERIC and the
ISBLANK code, the first just counts all the cells even the blanks, and
the second gives me an error when i run the program

Here is the code:
For C = Row To value
NotBlank = IsNumeric(range(f_column & C))
If NotBlank = True Then
NoBlanks = NoBlanks + 1
End If
Next C

Any ideas????
Thanks in advance,

dani


--
Dani
------------------------------------------------------------------------
Dani's Profile:
http://www.excelforum.com/member.php...o&userid=23542
View this thread: http://www.excelforum.com/showthread...hreadid=383153