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

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

"Sunil Patel" wrote:

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

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

"Greg Wilson" wrote:

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

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.

--
Regards,
Tom Ogilvy

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

"Sunil Patel" wrote:

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

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 wrote:

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.

--
Regards,
Tom Ogilvy

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

"Sunil Patel" wrote:

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




--

Dave Peterson

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.

--
regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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 wrote:

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.

--
Regards,
Tom Ogilvy

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

"Sunil Patel" wrote:

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




--

Dave Peterson