The following code found all instances of the substring "excel" within the
range A1:C10. The Selection and MsgBox code is for demo purposes only.

I think what you are looking for is the FindNext method. Note that, here,
"d" serves as the "After" argument to FindNext (i.e. FindNext(d)). In other
words, find the next cell containing the substring, skipping the first cell.
The code then sets d to this new cell and so forth. Also note that the
FindNext method will form a nonterminating loop, finding the first cell again
once it comes to the end of the range and repeating from there. That's why we
record the address of the first instance (adr) and stop it at this point.

Sub FindSubString()
Dim d As Range
Dim rng As Range
Dim cdnName As String
Dim adr As String

cdnName = "excel"
Set rng = Range("A1:C10")
Set d = rng.Find(cdnName)
If Not d Is Nothing Then
d.Select
MsgBox d.address
adr = d.Address ' record address of first instance
Do
Set d = rng.FindNext(d)
d.Select
MsgBox d.Address
Loop Until d.Address = adr ' stop when loops around to first instance
End If
End Sub


"Vik" wrote:

Hi All,

I am new in Excel programming.
I need to check if a cell contains a substring, something like "*excel*".

First, I tried

Set d = .Find(cdnName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

Only it looks like .Find cannot be used more than once. I have a nested loop
and used .Find in a wrapper loop.

I need some alternative to .Find.

Thanks!