.find with merged cell not working

[Sunil Patel]

Set ck = Range("A1:A10").Find("cat")
ck="cat" if cells not merged
but
ck is nothing when cell a2="cat" and cells a2,b2,c2 are merged

Any way of serching merged cells within a range for a text string

 

[Guest]

I confirm that the Find method does not find cells that are merged when the
merge area exceeds the search range. In the below code, the first option
(Option 1) does not work as you have pointed out. To use the Find method, you
need to include the merge area of merged cells within the search range.
Alternatively, you can use a loop but it is usually less efficient.

'Assumed is that cells A2:C2 are merged as per your example
Sub Find()
Dim ck As Range

Set ck = Range("A1:A10").Find("cat")
If Not ck Is Nothing Then MsgBox ck.Address & " Option 1"

Set ck = Range("A1, A2:C2, A3:A10").Find("cat")
If Not ck Is Nothing Then MsgBox ck.Address & " Option 2"

Set ck = Range("A1: C10").Find("cat")
If Not ck Is Nothing Then MsgBox ck.Address & " Option 3"

For Each ck In Range("A1:C10").Cells
If LCase(ck) = "cat" Then
MsgBox ck.Address & " Option 4"
Exit For
End If
Next

End Sub

Regards,
Greg

 

[Guest]

Correction to my post. I had intended to demonstrate that when you use a loop
it's not necessary to include entire merged ranges within the loops search
range. Only the active cell is necessary. The fourth option should have been:
For Each ck In Range("A1:A10").Cells 'Instead of A1:C10
If LCase(ck) = "cat" Then
MsgBox ck.Address & " 4"
Exit For
End If
Next

 

[Tom Ogilvy]

I can't confirm that. In xl97, this worked fine for me (cells A2:C2
merged)

Sub ABCD()
Set ck = Range("A1:A10").Find("cat")
MsgBox ck.Value & vbLf & ck.Address _
& vbLf & ck.MergeArea.Address

End Sub

Maybe it is one of the other settings of Find that is causing the problem.

 

[Dave Peterson]

I think the problem occurs in xl2k+ (not xl97). (I don't have xl97 anymore, but
that's the way I remember it.)

Saved from an earlier post:

Put test in d7, e5, f7
merge E5:E9

Ctrl-F
and Find test

Hit repeat Find a few times and watch the pattern of found cells.

If you're using xl2002+, hit Find All.

Works that way in code, too.

 

[Tom Ogilvy]

In xl2000, you are correct that the code I posted does not work. Manually,
If i have a single cell selected and it is not one of d7, e5, f7, then it
finds the merged cell first, then doesn't find it using findnext many times.