Report occurence of a string in text

[Ammarhm]

Hi
using the inStr function returns the position of the first word in a text. So let us say that I am excuting InStr(1, myString, "word"), then I will only get the position of the first word in the text
Any idea of what to do if I wanted to find all the positions of the word in the text if the word occurse more than once? It has to be some kind of a loop but i cant figure it out
THanks

 

[ChrisO]

Something to try:-

Sub TestIT()
    Dim lngPos() As Long
    Dim lngIndex As Long
    
    ReDim lngPos(0)

    SearchForWord "Word Cat  Wordsworth's Words", "word", lngPos()
    
    For lngIndex = LBound(lngPos()) To UBound(lngPos()) - 1
        MsgBox "Found at: " & lngPos(lngIndex)
    Next lngIndex

End Sub


Sub SearchForWord(ByVal strIn As String, _
                  ByVal strWord As String, _
                  ByRef lngPos() As Long)
                  
    Dim lngStart    As Long
    Dim lngPosition As Long
    
    If Len(strIn) > 0 And Len(strWord) > 0 Then
        lngStart = 1
        
        Do
            lngPosition = InStr(lngStart, strIn, strWord, vbTextCompare)
            If (lngPosition) Then
                lngPos(UBound(lngPos())) = lngPosition
                ReDim Preserve lngPos(UBound(lngPos()) + 1)
                lngStart = lngPosition + Len(strWord)
            End If
        Loop While lngPosition > 0
    End If
    
End Sub