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

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

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

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

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