I'm getting the "run-time error 13 - type mismatch"...

I'm trying to get at the value of a set of merged cells, except that
the cell my formula is going to a middle cell of the merged set, not
the left-most.

Cells C1:F1 are merged together and the value is the date 2/10/09

I'm trying to access the value of the cell, but may not necessarily be
using the left-most cell in the set of merged cells, like this:

Sub Testing()
x = Range("A1").Offset(0, 3).Value
MsgBox x
End Sub

Is there a better way to do this? So that if the code happens to call
at the middle of the merged cell set, it does not generate an error?

Try this one.

Sub Testing()
x = Range("A1").Offset(0, 2).Value
MsgBox x
End Sub

Offset does not include the base cell in the count, so offset(0, 3) would be
cell D1.




"MLT" wrote:

I'm getting the "run-time error 13 - type mismatch"...

I'm trying to get at the value of a set of merged cells, except that
the cell my formula is going to a middle cell of the merged set, not
the left-most.

Cells C1:F1 are merged together and the value is the date 2/10/09

I'm trying to access the value of the cell, but may not necessarily be
using the left-most cell in the set of merged cells, like this:

Sub Testing()
x = Range("A1").Offset(0, 3).Value
MsgBox x
End Sub

Is there a better way to do this? So that if the code happens to call
at the middle of the merged cell set, it does not generate an error?

The way this particular sheet is set up is that every 4 cells is
merged into one with a date in it. So G1:J1 and K1:N1 and so one
would be merged together and would have a date in each, say 2/10/09 in
G1:J1 and 2/11/09 in K1:N1.

Then, the cells going across in row 2 (which are not merged) will be
counted or not, based on the date of the column they are in. So for
example cells in columns G, H, I, and J would all correspond with the
date value in the G1:J1 merged cells, 2/10/09. That's why I'm trying
to find a way so that the cell gets the right date value even if the
column does not necessarily line up with the left-most cell of the
merged cell set.

The point is that to return the value in a merged range, you must refer to
the top left cell of the range. If your top left cell does not allign to a
column or row, it is difficult to set up a loop to do the job.
Unfortunately, the SpecialCells method does not include a merged cell
property. If the are all on row 1, at four cell intervals, and you have say
10 sets of merged cells, then you could use:

Dim i As Long
Set myRange = ActiveSheet.Range("A1:AN1")
For i = 1 To 40 Step 4
If Not i Is Nothing Then
x = Cells(1, i)
MsgBox x
End If
Next

"MLT" wrote:

The way this particular sheet is set up is that every 4 cells is
merged into one with a date in it. So G1:J1 and K1:N1 and so one
would be merged together and would have a date in each, say 2/10/09 in
G1:J1 and 2/11/09 in K1:N1.

Then, the cells going across in row 2 (which are not merged) will be
counted or not, based on the date of the column they are in. So for
example cells in columns G, H, I, and J would all correspond with the
date value in the G1:J1 merged cells, 2/10/09. That's why I'm trying
to find a way so that the cell gets the right date value even if the
column does not necessarily line up with the left-most cell of the
merged cell set.