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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

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

Thanks for the reply,

-Minitman



On Tue, 16 Sep 2008 17:51:15 -0700, "Jim Cone"
<(E-Mail Removed)> wrote:

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

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
<(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
>> 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