Hi,

The loop you are using is only checking the first occurance of the string
and returning the position in thestring not the count. The following appears
to do the job.

Sub getNum()

Const csFind As String = "ABC"
Dim lRow As Long
Dim sStr As String
Dim iStrCount As Integer, iStartPos As Integer, iPosInStr As Integer

For lRow = 2 To Cells(Rows.Count, "a").End(xlUp).Row
sStr = Cells(lRow, 1)
iStrCount = 0
iPosInStr = 0
Do
iPosInStr = InStr(iPosInStr + 1, sStr, csFind)
If iPosInStr < 0 Then iStrCount = iStrCount + 1
Loop Until iPosInStr = 0
Cells(lRow, 2) = iStrCount
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"GettingThere" wrote:

I'm trying to count the number of times a sequence of characters occurs in a
cell. The sequence could be surrounded by non-printing characters, and/or
could appear more that once in one string (ABCblahABC SDF ABC blah).

I tried the following, but it seems to "miss" sometimes. Any reason why?

Thanks in advance!

Sub getNum()
Dim i As Long
Dim str As String

For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
str = InStr(Cells(i, 1), "ABC")
Cells(i, "B") = str
Next i

End Sub