If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar

=SUMPRODUCT(--(A1:A10<""),--(NOT(ISNUMBER(A1:A10))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"F. Lawrence Kulchar" wrote in
message ...
If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar

Hi,

Why you don't use Worksheet function to this with:
=COUNTIF(A1:A6,"apple")

but if you want it programtically use:
sub countsomething()
dim j as long, v
v = 0
for j = 1 to 6
if cells(j,1).value= "apple" then v = v+1
next j
msgbox v
end sub

Regards,

halim


F. Lawrence Kulchar menuliskan:
If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar

"F. Lawrence Kulchar" skrev i
en meddelelse ...
If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar


One way:

Sub NumOfTextEntries()
'Leo Heuser, 4 Sept. 2006
Dim Cell As Range
Dim Counter As Long

For Each Cell In Worksheets("Sheet1").Range("A1:A6").Cells
If Application.IsText(Cell.Value) Then Counter = Counter + 1
Next Cell

MsgBox "Number of text entries: " & Counter
End Sub

--
Best regards
Leo Heuser

Followup to newsgroup only please.

PERFECT..thank you..

I understand it perfectly, but I NEVER could have created it!

Thank you,

FLKULCHAR

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A10<""),--(NOT(ISNUMBER(A1:A10))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"F. Lawrence Kulchar" wrote in
message ...
If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar