count instaces of text string in range

[Steve]

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

 

[NickHK]

Steve,
Check out INSTR in the VBA help.
Or you have the worksheet function SEARCH for an Excel solution.

NickHK

 

[Steve]

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

 

[NickHK]

Steve,
This assumes that "33QRyy jj" and "PROJECT 4338567 QR" are both acceptable.
Is that what you want.

NickHK

 

[Tom Ogilvy]

set rng = Range("B12:IV100")
msgbox Application.Countif(rng,"*QR*")