I am working on a code that automatically inserts formulas to perform calculations. The range that these formuals are inserted constantly changes and I need to use R1C1 formula references. I cannot figure out how to have "g" set as an absolute cell reference in the second For Each R1C1 formula. Any help is much appreciated.
Sub FindRow1()
Dim t As Range
Dim c As Range
Dim d As Range
Dim e As Range
Dim f As Range
Dim g As Range
With Worksheets("Recap Sheet").Cells
Set t = .Find("Year of Tax Return", After:=.Range("A1"),
LookIn:=xlValues).Cells
Set c = .Find("12. Total Gross Annual Cash Flow", After:=.Range("A1"),
LookIn:=xlValues).Cells
Set d = .Find("15. Total Annual Cash Flow Available to Service Debt",
After:=.Range("A1"), LookIn:=xlValues)
Set e = Range(t.Offset(1, 0), c.Offset(-1, 0))
Set f = Range(c.Offset(1, 0), d.Offset(-1, 0))
Set g = c.Offset(0, 9).Cells
For Each cell In e
If cell.Value <> "" Then
cell.Offset(0, 9).FormulaR1C1 =
"=average(R[0]C[-2],R[0]C[-4],R[0]C[-6],R[0]C[-8])"
End If
Next
For Each cell In e
If cell.Value <> "" Then
'& g & in the formula below does not equal the cell location set above
cell.Offset(0, 10).FormulaR1C1 = "=R[0]C[-1]/" & g & ""
End If
Next
For Each cell In f
If cell.Value <> "" Then
cell.Offset(0, 10).FormulaR1C1 =
"=average(R[0]C[-2],R[0]C[-4],R[0]C[-6],R[0]C[-8])"
End If
Next
For Each cell In f
If cell.Value <> "" Then
cell.Offset(0, 9).FormulaR1C1 = "=R[-2]C[0]*(R[0]C[1]/100)"
End If
Next
End With
End Sub
=SUM($A$1), it will translate that into the absolute reference in R1C1 style, e.g.=SUM(R1C1). And generally,R#C#refers to a specific Row and Column (i.e. absolute reference). When you doR[#]C[#], that#is a relative reference to the current cell. - BruceWayne