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

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

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

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" wrote in message
oups.com...
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

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

On Mar 19, 7:43 am, "Bob Phillips" wrote:
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" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Thank you so much, Bob! That works perfectly!