problem with find in merged cells

[Antonio]

I thought I had this VBA pretty under control, but this one is new to me.

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


Sheet:

Average Credit Balance 51,608.69 51,608.69 0

- Open Positions ( August 03, 2007 )
Stocks
Symbol Open Quantity Mult Open Price Close Price Cost Basis Value Unrealized
P/L
EUR
CS -- 7,000 1 -- -- 28.25 197,750.00 --
EAD -- 8,000 1 -- -- 21.92 175,360.00 --
FPB -- 5,000 1 -- -- 54.71 273,550.00 --

 

[Dave Peterson]

I'd bet that .find is supposed to work with merged cells. And that it's a bug
that it fails.

Another reason not to use merged cells?????

 

[Peter T]

Hi Antonio,

That works fine for me, ie your sample text in merged cells. It might not
work if:

- Previously set Find options are not what you require, eg Match case and/or
Entire Cells are ticked. Set options as required in code.

- The text does not exist in whatever range you qualified .Find with. Try
Activesheet.Cells

Regards,
Peter T

 

[Dave Peterson]

It failed for me when I specified all the parms.

Edit|Find even failed for me when I did this.

I started a new worksheet
I put asdf in A5
I selected A513
I clicked the Merge and Center icon on the formatting toolbar

I hit ctrl-f to see the Find dialog.
I typed asdf and hit the Find Next button
xl2003 came back with a couldn't find the data I'm search for message.

Oddly, if I hit the Find All button, it was found.

(Merged cells are a PITA.)

 

[JLatham]

Bad news ... as much as I dislike merged cells, I followed your steps
(xl2003, WinXP) and it found it - both with find next and Find All. Find All
even properly reported that it was in $A$5.

But the code definitely doesn't find it when set up as you did. It will
find it if the cells are not merged (and as long as the entry is really on
Sheets(1) - which mine wasn't for a while).

 

[Peter T]

Why is that bad news!
But your code failed, was that while the merged cell containing the text was
selected and no other similar text existed in the find range.
That's the only scenario I can get to fail, both manually and with code.

This workaround works for me, does it for you guys -

Sub Macro1()

Range("A513").HorizontalAlignment = xlCenter
Range("A513").Merge
Range("A513") = "hello Summer"

Range("A1").Select

End Sub

Sub FindTest()
Dim bFindBlank As Boolean
Dim rFound As Range
Dim sFindWhat As String

sFindWhat = "summer"

bFindBlank = False
reTryFind:
Set rFound = ActiveSheet.Cells.Find( _
What:=sFindWhat, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If rFound Is Nothing And (Not bFindBlank) Then
If ShtHasMergedCells(ActiveSheet) Then
bFindBlank = True
ActiveSheet.Cells.Find("").Select
GoTo reTryFind
End If
End If

If Not rFound Is Nothing Then
rFound.Parent.Activate
rFound.Select

MsgBox rFound.MergeArea.Address(0, 0)
Else
MsgBox "Not found"
End If

End Sub

Function ShtHasMergedCells(ws As Worksheet) As Boolean
Dim v As Variant

v = ws.Cells.MergeCells
If v = False Then
ShtHasMergedCells = False
Else
' Null or True
ShtHasMergedCells = True
End If

End Function

Regards,
Peter T

 

[JLatham]

'Bad news" because of inconsistent results under what should have been
duplicate test conditions.

Your workaround code did find it and reported the merge cell address
properly. Good deal.

 

[Antonio]

So is this a bug or not?

My workaround is just:

Application.Cells.MergeCells = False

'Bad news" because of inconsistent results under what should have been
duplicate test conditions.

Your workaround code did find it and reported the merge cell address
properly. Good deal.

 

[Antonio]

I definitively think this is a bug and that it should be reported to MS.

I am using xl2003 and xp2

The cell does contain the string I am looking for the Ctrl-F finds it but
the .find VBA function does not (even setting all the parameters correctly,
to make sure I just copied the same .find as one in the same code that did
work because the cell was not merged)

What is the process to report the bug.

 

[JLatham]

If you're coming in through Microsoft discussion forum of Excel, just start a
new thread and choose the "Suggestion for Microsoft" type of thread - that
will flag it for their attention. Describe the problem in detail and perhaps
even provide a link to the beginning of this discussion for reference.

If you're not coming in directly, you can get there through this link:
http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx#2
Just choose Excel, and probably the Application Errors group would be the
most logical place to post it:
http://www.microsoft.com/office/com...rosoft.public.excel.crashesgpfs&lang=en&cr=US

 

[Antonio]

done, thanks.

So is this a bug or not?

My workaround is just:

Application.Cells.MergeCells = False