Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count a number in a range with cells containing more than one numb | Excel Worksheet Functions | |||
Count the number of cells containing a date within a range | Excel Discussion (Misc queries) | |||
Count one character in a string across a range of cells? | Excel Worksheet Functions | |||
Count cells in column that contain number in a range | Excel Worksheet Functions | |||
Counting occurences of a substring in a range of cells. | Excel Worksheet Functions |