Excel VBA - Cells Counting Problem

  • Thread starter Thread starter wuming
  • Start date Start date
W

wuming

i know that to count the number of cells that contain any text you ca
use:
=COUNTIF(data,"*")

what if i want to differentiate the data as alphabetic data an
alphanumeric data?
eg. COUNTIF(A1:D4,"*") returns 16 when all cells have data like:
abc125 or abc

How do u differentiate data as alphanumeric (abc125) and alphabethi
(abc) data?? PLs help me
 
if the length of the numeric part is fixed (say 3 digits)
you can break the text string into the numeric part and
then take the value of it and do a countif(range,">0") on
that

=value(right(cell,3) will give you the value of the 3
digits in the 3 rightmost positions of the cell (assuming
there are 3 digits of course). If the cell has no digits,
the result of the expression wil be zero and not picked up
by the countif.

John
 
thanks for replying john. But the data i am counting are not fixe
meaning the codes wouldn't work, do u have other solutions?
abc125 can also be xxoo09496 or the data can vary
 
Hi Wumin,

To return the number of alpha cells, try the following function:

Function CountAlphas(CountRange As Range) As Long
Dim cell As Range
Dim iCtr As Long

For Each cell In CountRange
If Not IsEmpty(cell) Then
If Not cell.Value Like "*[0-9]*" Then
iCtr = iCtr + 1
End If
End If
Next
CountAlphas = iCtr
End Function
 
sry to bother u guys again but i have a problem:
i need to use the formula to count cells of up to 60k records++
and after using the codes given by Norman, the excel file hange
meaning that the codes cannot work for huge amount of datas. Do u guy
have any other alternatives?

its giving me a headache!!! :
 
Hi Wuming,

I called the function from a sub, using a range in excess of 16 million
cells. The only problem I experienced was the execution time.

I called the function from a worksheet cell, passing a range in excess of 1
million cells to the function. Again no problem except execution time.

Finally, using your problem example, I called the function from a worksheet
cell, passing the function a range of 61k cells. The function returned a
value in a fraction of a second.

I am sorry, therefore, that I cannot reproduce your problem.
 
Back
Top