The number of cells in a column until you reach a certain
text phrase is equal to the row number that the text
phrase is found in minus 1. The following code will
determine this row number. Just subtract 1 from it. The
code assumes that the column is column A and the text
phrase is "monkey". Change to suit. Change the
argument "xlWhole" to "xlPart" if the text phrase forms
only part of the cell value or their are potentially blank
spaces included.

Sub Test()
Dim rng As Range, C As Range
Dim rw As Long, txt As String
txt = "monkey"
rw = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(rw, 1))
Set C = rng.Find(What:=txt, LookIn:=xlValues, _
LookAt:=xlWhole)
rw = C.Row
MsgBox rw
End Sub

Be advised that I did respond to your other request in the
Miscellaneous newsgroup. I rewrote the code I gave you
yesterday. My experiments confirmed that it works at least
according to my understanding of the problem. I can only
respond in the evening because I work during the day.

Regards,
Greg

-----Original Message-----
I greatly appreciate your help. With a few tweaks, I
made afew changes
and am well on my way.

I have to admit I am pretty embarrassed that I was not
able to decipher
such a code myself. My attempts at this were much more
complicated.

Do you know a way that I can count how many cells are in
a column until
i reach a certain value (which is identified by a text
phrase).

Thanks again,

Brock


---
Message posted from http://www.ExcelForum.com/

.