Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count instaces of text string in range
hi all,
your thoughts please? I need to count instances of a text string, in this example "QR", across my range. However the "QR" may exists within another string eg. cell A1 - PROJECT 4338567 QR complete cell a2 - 433756 testing QR etc the below is clearly wrong, but it may give you an idea of what I need to acheive for each cell in Range(cells(2, 2), Cells(20, 20)).Cells if cell.value CONTAINS "QR" then qrcount = qrcount + 1 end if next msgbox qrrcount excel 97 any help appreciated :) steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count instaces of text string in range
Steve,
Check out INSTR in the VBA help. Or you have the worksheet function SEARCH for an Excel solution. NickHK "Steve" wrote in message ... hi all, your thoughts please? I need to count instances of a text string, in this example "QR", across my range. However the "QR" may exists within another string eg. cell A1 - PROJECT 4338567 QR complete cell a2 - 433756 testing QR etc the below is clearly wrong, but it may give you an idea of what I need to acheive for each cell in Range(cells(2, 2), Cells(20, 20)).Cells if cell.value CONTAINS "QR" then qrcount = qrcount + 1 end if next msgbox qrrcount excel 97 any help appreciated :) steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count instaces of text string in range
nice tip Nick, thanks very much!
my code is now For Each cell In Range("b12:iv100").Cells If InStr(1, cell.Value, "QR") 0 Then qrcount = qrcount + 1 End If Next MsgBox qrcount On Thu, 17 Feb 2005 12:42:32 +0800, "NickHK" wrote: Steve, Check out INSTR in the VBA help. Or you have the worksheet function SEARCH for an Excel solution. NickHK "Steve" wrote in message . .. hi all, your thoughts please? I need to count instances of a text string, in this example "QR", across my range. However the "QR" may exists within another string eg. cell A1 - PROJECT 4338567 QR complete cell a2 - 433756 testing QR etc the below is clearly wrong, but it may give you an idea of what I need to acheive for each cell in Range(cells(2, 2), Cells(20, 20)).Cells if cell.value CONTAINS "QR" then qrcount = qrcount + 1 end if next msgbox qrrcount excel 97 any help appreciated :) steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
count instaces of text string in range
Steve,
This assumes that "33QRyy jj" and "PROJECT 4338567 QR" are both acceptable. Is that what you want. NickHK "Steve" wrote in message ... nice tip Nick, thanks very much! my code is now For Each cell In Range("b12:iv100").Cells If InStr(1, cell.Value, "QR") 0 Then qrcount = qrcount + 1 End If Next MsgBox qrcount On Thu, 17 Feb 2005 12:42:32 +0800, "NickHK" wrote: Steve, Check out INSTR in the VBA help. Or you have the worksheet function SEARCH for an Excel solution. NickHK "Steve" wrote in message . .. hi all, your thoughts please? I need to count instances of a text string, in this example "QR", across my range. However the "QR" may exists within another string eg. cell A1 - PROJECT 4338567 QR complete cell a2 - 433756 testing QR etc the below is clearly wrong, but it may give you an idea of what I need to acheive for each cell in Range(cells(2, 2), Cells(20, 20)).Cells if cell.value CONTAINS "QR" then qrcount = qrcount + 1 end if next msgbox qrrcount excel 97 any help appreciated :) steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
count instaces of text string in range
set rng = Range("B12:IV100")
msgbox Application.Countif(rng,"*QR*") -- Regards, Tom Ogilvy "Steve" wrote in message ... nice tip Nick, thanks very much! my code is now For Each cell In Range("b12:iv100").Cells If InStr(1, cell.Value, "QR") 0 Then qrcount = qrcount + 1 End If Next MsgBox qrcount On Thu, 17 Feb 2005 12:42:32 +0800, "NickHK" wrote: Steve, Check out INSTR in the VBA help. Or you have the worksheet function SEARCH for an Excel solution. NickHK "Steve" wrote in message . .. hi all, your thoughts please? I need to count instances of a text string, in this example "QR", across my range. However the "QR" may exists within another string eg. cell A1 - PROJECT 4338567 QR complete cell a2 - 433756 testing QR etc the below is clearly wrong, but it may give you an idea of what I need to acheive for each cell in Range(cells(2, 2), Cells(20, 20)).Cells if cell.value CONTAINS "QR" then qrcount = qrcount + 1 end if next msgbox qrrcount excel 97 any help appreciated :) steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count one character in a string across a range of cells? | Excel Worksheet Functions | |||
Count occurance of a leter in a text string | Excel Worksheet Functions | |||
Count Instances of Text in String Q | Excel Worksheet Functions | |||
Sum / count data from text string with delimiter | Excel Worksheet Functions | |||
Locate and count the recurrences of a text string | Excel Discussion (Misc queries) |