CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-r1c1-notation
Deglupta
Deglupta

Reputation: 25

R1C1 Formula result gives twice the given variable

I want to normalize some data and want this normalized data to change dynamically when the data changes, that's why I want to put Formula in Cells that reference different Cells. But that's not really relevant to the problem because even the simplest of Formula (shown below) doesn't work.

Public Sub Test()
    a = 15
    Cells(a, 8).FormulaR1C1Local = "=Z(" & a & ")S7"
End Sub

The Formula in the Cell(15,8) is =$G30.
I expected the following output: =$G15.

In many forums where people ask how to put variables in a R1C1Formula the answer is: [" & a & "], but this gives a runtime error 1004, so I tried () instead and it doesn't give me any warnings, but the result is not what I expected.

Upvotes: 0

Views: 33

Answers (1)

Axel Richter
Axel Richter

Reputation: 61880

You need understand the R1C1 rules in terms of relative and absolute references.

R15C7 is absolute: Row 15 and Column 7 = $G$15.

R[15]C7 is row relative and means: The row where the formula is in plus 15 rows and Column 7. If formula is in row 15, then = $G30.

RC7 means: The row where the formula is in and Column 7. If formula is in row 15, then = $G15.

Public Sub Test()
 a = 15
 Cells(a, 8).FormulaR1C1 = "=R[" & a & "]C7" '$G30
 Cells(a, 8).FormulaR1C1 = "=R" & a & "C7"   '$G$15
 Cells(a, 8).FormulaR1C1 = "=RC7"            '$G15
End Sub

Translated to your locale Zeile/Spalte Z1S1:

Public Sub Test()
 a = 15
 Cells(a, 8).FormulaR1C1Local = "=Z(" & a & ")S7" '$G30
 Cells(a, 8).FormulaR1C1Local = "=Z" & a & "S7"   '$G$15
 Cells(a, 8).FormulaR1C1Local = "=ZS7"            '$G15
End Sub

Upvotes: 1

Related Questions