Find Address of merged range VBA

[jlclyde]

I am trying to return the address of the top left cell in a merged
range. thsi is what I have so far and it is not even close to right.

Set Rng = Range("A52:BS52").Find(what:=ComboBox1.Text,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)

MsgBox Rng.MergeArea.Resize(1, 1).Address

Thanks,
Jay

 

[Bernie Deitrick]

Jay,

If Rng.MergeCells Then
MsgBox Rng.MergeArea.Cells(1, 1).Address
Else
MsgBox Rng.Cells(1, 1).Address
End If

HTH,
Bernie
MS Excel MVP

 

[jlclyde]

If Rng.MergeCells Then
MsgBox Rng.MergeArea.Cells(1, 1).Address
Else
MsgBox Rng.Cells(1, 1).Address
End If

HTH,
Bernie
MS Excel MVP

Bernie,
After closer inspection it is nto finding the value from the combobox1
in merged cells. I realize now that I have to tell it to find in rows
52 through 54. Then it works perfectly. You got me pushed in the
right direction though.

Thanks,
Jay

If UCase(Right(Left(ComboBox1.Text, 5), 3)) = "RUN" Or _
ComboBox1.Text = "UpComing Kluge" Then
Set Rng = Range("A52:BS54").Find(what:=ComboBox1,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)
Else
Set Rng = Range("A2:FK2").Find(what:=ComboBox1,
lookat:=xlWhole, _
Searchdirection:=xlPrevious, MatchCase:=False)
End If