Hello All,

I am trying to search a range to find all the cells that contain a
string with a specified substring. I know that I could do this by
looping through each cell in the range and testing:

For Each rCell In SearchRange.Cells
If InStr(rCell.Value, Substring) <> 0 Then
...

, but I was wondering if there was a faster way of doing this. For
instance, is there some way I could count all the cells that do
contain strings with the specified substring and then limit the loop
iterations to that number? That would save me many iterations if the
cells containing strings with the specified substring are at the top
of the range or if no cell contains a string with the specified
substring. Perhaps the code would look something like this:

HowMany = Application.CountIf(SearchRange,
InStr(SearchRange.Cells.Value, Substring))

I am running Excel 2003 and Visual Basic 6.3. I appreciate any effort
to help me. Thank you in advance for your time and consideration.

Sisilla

Sub routine()
MsgBox (Application.WorksheetFunction.CountIf(Selection, "*e*"))
End Sub


will indicate how many cell in Selection contain an e within them. Adapt as
you wish.


b.t.w works the same way on the worksheet.
--
Gary''s Student
gsnu200711