Offset to merged cell doesn't work

[roelc]

Hi,

On my sheet columns K:M are merged cells (only horizontally merged)
The same applies to columns N:P

I'm trying to give a variable column offset to cells/ranges in column K with Integer Ccol1
In this example, Ccol1 = 3
So K15 as starting point should result in N15 where I want to set a value.

For debugging I'm checking the addresses determined:

Debug.Print sht2.Range("K18", "K28").Offset(0, Ccol1).Address

gives me $N$18:$N$28 which is what I expect and works perfectly

However,

Debug.Print sht2.Range("K15", "K15").Offset(0, Ccol1).Address

gives me $P$15 instead of $N$15.
Setting the value of P15 does not show in the merged cell N15:P15

What am I doing wrong here?

 

[Norie]

Are you sure you have the same merged cells in row 15 as you have in rows 18 to 28?

 

[roelc]

Are you sure you have the same merged cells in row 15 as you have in rows 18 to 28?

yes, merging is the same in all those cells.
The "unexpected" behavior happens when the starting point is a range containing only one merged cell, no matter which cell. It seems that in such case Excel takes the right-most cell of the merged range as starting point for counting the offset.
When the starting point is a range of multiple cells, it works as it should.

For example

Debug.Print ActiveSheet.Range("K16", "K17").Offset(0, 3).Address

gives $N$16:$N$17

Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address

gives $P$16

Only if I unmerge cells K16:K18, then

Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address

gives $N$16

 

[roelc]

Apologies, last sentence should be:

Only if I unmerge cells K16:M16, then

Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address

gives $N$16

 

[roelc]

I now "solved" the problem by adding MergeArea on the destination. Since the destination is also in merged cell (columns N:P), adding the mergearea will set destination to column N.

Debug.Print ActiveSheet.Range("K15","K15").Offset(0,Ccol1).MergeArea(1,1).Address

It's not a very nice solution, but it works for me in this case.
Somehow still can't find a better solution where it really counts the offset starting from column K even though it's a merged cell.
If starting range includes multiple cells ("K16","K17"), the offset starts to count from column K as expected.
Even if starting range includes multiple cells within one merged cell ("K16", "L16"), the offset starts to count from column K as expected

But only if the starting range is one single cell that's part of a merged cell, like ("K16","K16") or even ("L16,"L16"), the offset starts to count from the last column of that merged cell, which is column M

It's very strange behavior, since:

Debug.Print ActiveSheet.Range("K16", "K16").Address

gives $K$16, but somehow the offset only starts to count from the rightmost column of the merged cell