Excel Copy column is not working properly

Question

I am working on an Excel Add-in, and requirement was to copy one entire column and paste to some new location. I used range.copyFrom() function to copy, but it was not copying all formulas correctly. If Address of a cell referenced in formula is written as C$1 or C1 and new paste location is 2 columns right to original cell, then formula is updated to E$1 or E1, which is as per expectation. However if address is written as $C1 then it remains $C1 in new column instead of $E1.

After that I manually tried to copy column and its behavior was same. So if a cell address is referred as $C1 then either Excel should not accept it as valid cell or it should also update this format address in pasted formulas also.

Answer 1

Hi Kashif,

Referencing to this doc: https://support.microsoft.com/en-us/office/switch-between-relative-and-absolute-references-981f5871-7864-42cc-b3f0-41ffa10cc6fc, the $C1 is also valid for the formula, which is to absolute column and relative row. Hope this could answer your question.

Thanks,

Dongqi