View Single Post
Old 01-20-2017, 01:19 AM
Filip88 Filip88 is offline Windows 10 Office 2016
Join Date: Jan 2017
Posts: 12
Filip88 is on a distinguished road
Default Referencing to cells by FormulaR1C1


I am trying to program a code with FormulaR1C1, referencing to cell that would be moved when I scale down the formula.

In cells C2 and D2, I am having data, added in variables row and cell. in variable C6, I am having FormulaR1C1 with variables row and cell (including data from C2 and D2).

In C6, I have formula referencing to cell A4, when I want to scale down cell C6 -> C7 - > C8 with this formula, I would like to scale down also the referenced cell A4 -> A5 -> A6.

- cell C6 refers to A4
- cell C7 refers to A5
- cell C8 refers to cell A6

The problem is that the cell is always fixed in the same row, where formula is, so cell C6 refers to A6, cell C7 refers to A7 etc.

My code:

Sub MakroTest1()
row = D2
coll = [C2]
Worksheets("List1").Range("C6").FormulaR1C1 = "=R" & row & "C" & coll & ""
End Sub
Would you have any suggestion how to move referenced cell with this formulaR1C1? I have attached the excel file and print screen.

I am really lost. Thank you

Attached Images
File Type: jpg image.jpg (166.9 KB, 11 views)
Attached Files
File Type: xlsm Macro_test.xlsm (16.8 KB, 8 views)
Reply With Quote