Find Address of merged range VBA
 

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

Find Address of merged range VBA
 

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

Find Address of merged range VBA
 

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