Finding a string within a string multiple times

[jdilts]

The title is kind of confusing.
What I mean is let's say I have a string in a cell
"Want food? food please."

I search for food.
InStr(1,"Want food? food please","food")

How do I get both positions of where both "food"s are located? Instead of just one.(I believe InStr only gives me the location of the first "food" and not the second one. Correct me if I'm wrong.).

Thanks!

[Bob Phillips]

Sub FindAll()
Dim mText As String
Dim mSearch As String
Dim mOut As String
Dim pos As Long

mText = "Want food? food please"
mSearch = "food"
pos = 1
Do While pos > 0

pos = InStr(pos, mText, mSearch)
If pos > 0 Then

mOut = mOut & pos & ","
pos = pos + 1
End If
Loop

If mOut <> "" Then

MsgBox Left$(mOut, Len(mOut) - 1)
End If
End Sub

[snb]

Sub M_snb()
sn = Split("Want food? food please", "food")

For j = 0 To UBound(sn) - 1
x = x + Len(sn(j)) + IIf(j > 0, Len("food"), 0)
y = y & ", " & x + 1
Next

MsgBox Mid(y, 3)
End Sub

[Teeroy]

Not as concise, but as a general Function to output an array

Function MatchAll(SWhole As String, sSearch As String)
Dim posn(), vParts
Dim temp As Integer, i As Integer

vParts = Split(SWhole, sSearch)
If UBound(vParts) > LBound(vParts) Then
ReDim posn(LBound(vParts) To UBound(vParts) - 1)
For i = LBound(vParts) To UBound(vParts) - 1
temp = Len(vParts(i)) + temp
posn(i) = temp + 1
temp = temp + Len(sSearch)
Next i
Else
ReDim posn(0)
posn(0) = 0
End If
MatchAll = posn
End Function

[jdilts]

Wow! Thank you everyone.