Count the number of cells in a range with a string containing a specified substring

[Sisilla]

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

 

[Bob Phillips]

Why not just

HowMany = Application.Countif(SearchRange, "*" & Substring & "*")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sisilla]

Why not just

HowMany = Application.Countif(SearchRange, "*" & Substring & "*")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)












- Show quoted text -

Thank you so much, Bob! That works perfectly!