Greg,

Let me be more specific.

Let's say I have a datasheet as follows.
____A______B__
1 aaaa
2 aaaa exc1
3 aaaa 3exc
4 bbbb exc2
5 bbbb
6 bbbb

Below is my macro -

Sub test()
Sheets("TEST").Activate
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range
Dim d As Range
Set rng1 = ActiveSheet.Range("A1:A6")
Set c = rng1.Find("aaaa", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)
If Not c Is Nothing Then
firstAddress = c.Address
cntAaaE = 0
cntAaaNoE = 0
Do
myRow = c.Row
Set rng2 = ActiveSheet.Cells(myRow, 2)
Set d = rng2.Find("exc", LookIn:=xlValues, LookAt:=xlPart,
MatchCase:=False)
If Not d Is Nothing Then
cntAaaE = cntAaaE + 1
Else
cntAaaNoE = cntAaaNoE + 1
End If
Set c = rng1.FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
MsgBox "cntAaaE=" & cntAaaE & ", cntAaaNoE=" & cntAaaNoE
End Sub

I expected it to print "cntAaaE=2, cntAaaNoE=1".
I need to count lines that match "aaaa" in a 1st column AND having "exc" as
a part of a value in a 2nd column.
But unfortunately, rng1.FindNext(c) does not do what I want.

Appreciate your help.

"Greg Wilson" wrote in message
...
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!