Offset to merged cell doesn't work

roelc

New Member
Joined
Mar 9, 2013
Messages
7
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:

Code:
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,

Code:
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
Are you sure you have the same merged cells in row 15 as you have in rows 18 to 28?
 

roelc

New Member
Joined
Mar 9, 2013
Messages
7
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
Code:
Debug.Print ActiveSheet.Range("K16", "K17").Offset(0, 3).Address
gives $N$16:$N$17

Code:
Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address
gives $P$16

Only if I unmerge cells K16:K18, then
Code:
Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address
gives $N$16
 

roelc

New Member
Joined
Mar 9, 2013
Messages
7
Apologies, last sentence should be:

Only if I unmerge cells K16:M16, then

Code:
Debug.Print ActiveSheet.Range("K16", "K16").Offset(0, 3).Address
gives $N$16
 
Last edited:

roelc

New Member
Joined
Mar 9, 2013
Messages
7
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.

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

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

Watch MrExcel Video

Forum statistics

Threads
1,108,620
Messages
5,523,944
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top