#1
|
|||
|
|||
Referencing to cells by FormulaR1C1
Hello,
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: Code:
Sub MakroTest1() row = D2 coll = [C2] Worksheets("List1").Range("C6").FormulaR1C1 = "=R" & row & "C" & coll & "" End Sub I am really lost. Thank you Filip |
#2
|
|||
|
|||
Quote:
Code:
Sub MacroTest2() With Worksheets("List1") ro = Range("d2").Value col = Range("c2").Value .Range("C6").Formula = "=$" & ColumnLetter(CLng(col)) & ro End With End Sub Code:
Function ColumnLetter(ColumnNumber As Long) As String Dim n As Long, c As Byte, s As String n = ColumnNumber Do c = ((n - 1) Mod 26) s = Chr(c + 65) & s n = (n - c) \ 26 Loop While n > 0 ColumnLetter = s End Function |
#3
|
|||
|
|||
I tried your UDF and it works, thank you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formating all cells in an array based on adjacent cells | deejay | Excel | 6 | 12-20-2016 12:00 PM |
Linking cells: can sub-cells move with their master-cell when sorting? | kw01 | Excel | 1 | 06-30-2015 05:02 PM |
Copying text range of cells to different cells adds an extra line | jpb103 | Word VBA | 2 | 07-23-2014 12:22 PM |
Naming Cells for formula referencing | lynchbro | Excel | 6 | 06-26-2014 07:45 AM |
Referencing | darksupernova | Word | 4 | 11-08-2012 03:37 AM |