CODEWITHSUNDEEP
excelvba
Patrick
Patrick

Reputation: 21

R1C1 Formula Format with Variable Fixed Cell

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

Upvotes: 0

Views: 1589

Answers (1)

GSerg
GSerg

Reputation: 78185

cell.Offset(0, 10).FormulaR1C1 = "=R[0]C[-1]/" & g.Address(True, True, xlR1C1)

Upvotes: 4

Related Questions