VBA Find function does not work for merged cells. Potential BUG

[Antonio]

As per this thread

http://www.microsoft.com/office/com...ming&mid=096f5059-3c5c-4e9c-a7ba-1ed27880f1b5



set r = .Find(" - Open Positions ( August 03, 2007 )")

returns nothing even though there is cell with that value

The only peculiarity of that cell is that is merged with others

Is the find function not supposed to work for merged cells?

Thanks,

Antonio


Code and sheet follows:

Sub main()

Dim r As Range

Dim match_address As String


Dim st As String


st = Worksheets(1).Range("A3").Value



With Worksheets(1).Columns("A")



Set r = .Find(st)

If Not r Is Nothing Then

match_address = r.Address



End If

End With

End Sub




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...2bd1218&dg=microsoft.public.excel.programming

 

[Tom Ogilvy]

Sub Macro1()
Set rng = Selection.Find( _
What:=" - Open Positions ( August 03, 2007 )", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select
End Sub

worked fine for me when I had that string entered in cells that were merged.

The argument settings can make a difference. Best to specify what you want.

Note that the string you are searching for does lead of with a space
character, so make sure you have entered the string to match something you
have in the sheet. You might test it with something less involved to make
sure.

 

[Antonio]

It does not work even specifying all parameters

The following code

Sub main()

Dim r As Range

Dim match_address As String


With Worksheets(1).Columns("A")



Set r = .Find("abc")

If Not r Is Nothing Then

match_address = r.Address



End If

End With

End Sub



does not find "abc" if "abc" in in column A in a merged cell, merged with
the next cell to the right

 

[Tom Ogilvy]

I think the trick is that you have to include at least the entire merged area
in your search area. For example, in the cell containing the "abc", it was
a merge area of 3 columns and two rows (A16:C17). When I expanded your
Columns("A") to include columns A:C, it worked OK.

Sub main()
Dim r As Range
Dim match_address As String
With Worksheets(1).Columns("A:C")
Set r = .Find(What:="ABC", _
After:=Worksheets(1).Range("A65536"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
r.Select
If Not r Is Nothing Then
match_address = r.Address
Debug.Print "Found at: " & match_address
End If
End With
End Sub

 

[Antonio]

That is not an option when you do not know in advance the merge structure

 

[Peter T]

Didn't the workaround I suggested in your other post work for you, doesn't
require knowing in advance the merge structure, if any.

Regards,
Peter T

 

[Tom Ogilvy]

Hello Peter,

xl2003:
I didn't find it necessary to select the sheet.

Just using CELLS was sufficient although overkill. My suggestion had to do
with the fact that he wanted to only look in column A.

Using either cells or adding additional columns, the OP could just check if
the found value was in column A and search again if it isn't. Sample code
for that is in the FINDNEXT VBA help.

--
Regards,
Tom Ogilvy

 

[Peter T]

Hi Tom,

When I tested yesterday (eventually) I found a bug in the following scenario

- the find string is in merged cells
- the merged area is selected
- only one instance of the find string in the find range

My workaround was, if at first Find didn't find, select a different cell, I
used Find(""), and try again.

I just tried your code, it worked perfectly. I amended to .Cells, ie whole
sheet, and that also worked fine.
What seems to make it work is this argument -
After:=Worksheets(1).Range("A65536")

This is much cleaner than my clunky idea!

Antonio,
I know the purpose of this thread is to point out the bug to MS. In the
meantime I would suggest exploring Tom's suggestion further, or even mine.
Should work without needing to "know in advance the merge structure"

Regards,
Peter T