VBA FormulaR1C1wrong column from column AA

Asked Sep 27 '22 at 17:23

Active Sep 27 '22 at 17:28

Viewed 31 times

I am using the below code to add a formula to a cell:

Cells(1, 1).FormulaR1C1 = 
"=RC" & Range("Col2").Column & 
"-RC" & Range("Col28").Column & 
"-RC" & Range("Col54").Column

What I'm expecting in Cell A1:

=B1-AB1-BB1

What happens is:

=B1-AC1-BD1

From column AA it is shifted by one column and from column BA by two columns.

In which area (A to Z or AA to AZ or BA to BZ) the linked cells are located is only known at runtime. Therefore it does NOT work:

Cells(1,1).FormulaR1C1 = 
"=RC" & Range("Col2").Column & 
"-RC" & Range("Col28").Column -1 &
"-RC" & Range("Col54").Column -2

edited Sep 27 '22 at 17:28

asked Sep 27 '22 at 17:23

Änna

1

double check that you are passing the correct column number. Maybe your named range is not pointing at what you think it is. – Scott Craner Sep 27 '22 at 17:26
oh and btw, Column AP is column 42 not 41. – Scott Craner Sep 27 '22 at 17:28
thanks, 've simplified it now. – Änna Sep 27 '22 at 17:30
1

I assume those are named ranges. Can we see how they are defined? – Scott Craner Sep 27 '22 at 17:31
2

^^^ if they are not names ranges, you may be looking for `Columns(2).Column` though at that point you may as well just use `2`, `28` and `54` – cybernetic.nomad Sep 27 '22 at 17:51
4

What's the output from `Debug.Print Range("Col2").Column, Range("Col28").Column, Range("Col54").Column` ? – Tim Williams Sep 27 '22 at 17:51
Yes, the cells are named because the location of the cell always changes depending on how many entries precede the cell in the same row. The Debug Print says 2, 28 and 54. But the cell then suddenly contains the incorrect formula. – Änna Sep 28 '22 at 08:12

0 Answers0