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

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
--


Gary


"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.

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

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


On Tue, 16 Sep 2008 20:35:28 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

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

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


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


On Tue, 16 Sep 2008 20:35:28 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

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

meant dave's code.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


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


On Tue, 16 Sep 2008 20:35:28 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

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

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




On Wed, 17 Sep 2008 01:54:15 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

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

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

Hey Jim,

Thanks for the reply,

-Minitman



On Tue, 16 Sep 2008 17:51:15 -0700, "Jim Cone"
wrote:


The top left cell, in a set of merged cells, is the only cell that
can contain a value.

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 wrote:

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

--

Dave Peterson

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

On Tue, 16 Sep 2008 20:35:09 -0500, Dave Peterson
wrote:

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 wrote:

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