Accessing The Value Of A Merged Cell

[Minitman]

Greetings,

I have a set of merged cells in column A. These merged cells are 1
column wide and 27 rows high. They have dates as values, displayed at
a 90 degree angle with a large font. I need to access the value from
any one of the 27 rows that the merged cell is sitting in. I am
trying to use the Worksheet_BeforeDoubleClick event on the cells in
column C and offset the focus from the target to the merged cell in
column A to capture the date. I can't seem to get it to display the
date.

Here is my code:

_______________________________________________________
Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Dim myRng

If Target.Column = 3 Then
myRng = Target.Offset(0, -2)
MsgBox "Date = " & myRng
End If
End Sub
_______________________________________________________

If I change the column offset, it works as expected. It appears that
vba can't recognize the merged cell.

Anyone have any idea as to how to get the code to see the value in the
merged cell?

Any help would be appreciated.

-Minitman

 

[Gary Keramidas]

if you type this in the immediate window, it should return the merged range:
?range("C5").Offset(,-2).mergearea.Address

then you could use the split function to get the value

try this:

Sub test()
Dim dval As Date
dval = Range(Split(Range("C5").Offset(, -2).MergeArea.Address, ":")(0)).Value
MsgBox dval
End Sub

 

[Jim Cone]

The top left cell, in a set of merged cells, is the only cell that
can contain a value.
--
Jim Cone
Portland, Oregon USA



"Minitman"
wrote in message
Greetings,
I have a set of merged cells in column A. These merged cells are 1
column wide and 27 rows high. They have dates as values, displayed at
a 90 degree angle with a large font. I need to access the value from
any one of the 27 rows that the merged cell is sitting in. I am
trying to use the Worksheet_BeforeDoubleClick event on the cells in
column C and offset the focus from the target to the merged cell in
column A to capture the date. I can't seem to get it to display the
date.
-snip-
If I change the column offset, it works as expected. It appears that
vba can't recognize the merged cell.
Anyone have any idea as to how to get the code to see the value in the
merged cell?
Any help would be appreciated.
-Minitman

 

[Dave Peterson]

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myRng As Range 'not as variant

If Target.Column = 3 Then
Set myRng = Target.Offset(0, -2).MergeArea.Cells(1)
MsgBox "Date = " & myRng.Value
End If

End Sub

 

[Minitman]

Hey Gary,

Thanks for the reply.

I'm not sure if your code is usable in this instance. I'm not sure how
to hard code the cell address of the merged cell, when it can be 1 of
31 different cells.

Thanks for the effort, it is appreciated.

-Minitman

 

[Minitman]

Hey Jim,

Thanks for the reply,

-Minitman

 

[Minitman]

Hey Dave,

Thanks for the reply.

I like your solution!

I had come up with this work around:
_____________________________________________________

myRng = Target.Offset(-(Target.Offset(0, -1)) + 1, -2)
(where the first column is the item number (1 - 27) for that day).
_____________________________________________________

Your code is exactly what I was looking for and it is easier to read
then what I came up with. Thank you.
_____________________________________________________

Set myRng = Target.Offset(0, -2).MergeArea.Cells(1)
_____________________________________________________

I could not get MergeArea to work before you posted this code! I am
still a bit confused by Cells() I couldn't find it in the MS Help
under cells.

Again, thank you very much.

-Minitman

 

[Gary Keramidas]

this wouldn't work? or adapt jim's code to it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Dim myRng

If Target.Column = 3 Then
myRng = Range(Split(Target.Offset(, -2).MergeArea.Address,
":")(0)).Value
MsgBox "Date = " & myRng
End If
End Sub

 

[Gary Keramidas]

meant dave's code.

--


Gary

this wouldn't work? or adapt jim's code to it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Dim myRng

If Target.Column = 3 Then
myRng = Range(Split(Target.Offset(, -2).MergeArea.Address,
":")(0)).Value
MsgBox "Date = " & myRng
End If
End Sub

 

[Minitman]

Hey Gary,

I tried your solution and it also works.

Thank you.

That makes three different solutions to this question that work.

My thanks to all of you who have taken the time to work on this
problem, it is greatly appreciated. :^)

-Minitman