Referencing to cells by FormulaR1C1

Filip88 · #1 01-20-2017, 01:19 AM

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

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

Filip

NoSparks · #2 01-20-2017, 04:47 PM

Quote:

Would you have any suggestion how to move referenced cell with this formulaR1C1?

No. But if you're willing to use a 'normal' formula, this macro along with the UDF, will put a drag-able formula into C6.

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

Filip88 · #3 01-21-2017, 07:51 AM

Quote:

Originally Posted by NoSparks

No. But if you're willing to use a 'normal' formula, this macro along with the UDF, will put a drag-able formula into C6.

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