Hi folks,

Ok this is the problem
In column A cells 1 to 5 are merged and display a single
word which was originally in cell A1 but is now centered
vertically.
in column D cells 1 to 5 is a list of names. I need excel
to find the name and then display the word in Label1

So far I've used the find method successfully to locate
the name but have come unstuck on the next bit.

'Myname = the found name's address

label1.caption = range(Myname).end(xlToLeft).text
'(ie label1 displays range A 1-5 which ever correpsonds to
the found cell.

This works, but because range A1-5 is merged, the word is
only in range A1 so label1 displays nothing

Hi Libby
This works for me:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target & [A1]
End Sub

whe
first five cells in column A are merged
first five cells in column D contain a different word each
this routine is placed in the Sheet1 object

When you click on a cell in column D a message appears showing the target
and what is in cell A1.


HTH
Andrew B



"Libby" < wrote in message ...
Hi folks,

Ok this is the problem
In column A cells 1 to 5 are merged and display a single
word which was originally in cell A1 but is now centered
vertically.
in column D cells 1 to 5 is a list of names. I need excel
to find the name and then display the word in Label1

So far I've used the find method successfully to locate
the name but have come unstuck on the next bit.

'Myname = the found name's address

label1.caption = range(Myname).end(xlToLeft).text
'(ie label1 displays range A 1-5 which ever correpsonds to
the found cell.

This works, but because range A1-5 is merged, the word is
only in range A1 so label1 displays nothing