CODEWITHSUNDEEP
vbaexcel
JayJayAbrams
JayJayAbrams

Reputation: 195

excel vba | change absolute cells in loop

Hiyall!

How to change formula absolute cells in loop? Lets say I have link =ref!$C$2*4, how to move in 1 row lower for next run in the loop =ref!$C$3*4? Do I need to split it and add counter? Or there is an easier way?

sample code

Dim cycle As Range
For Each cycle In Sheets("ref").Range("A2:A4")
    ...
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=ref!R2C2" + "*4"
    Selection.AutoFill Destination:=Range("D2:D3")
    ...
End Sub

Also I am curious how to add math formulas in VBA like =(С4+B4)/LOG10(A4) + 12, using quote marks did not help at all :<

Upvotes: 0

Views: 140

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

Use Formula rather than FormulaR1C1

Sub dural()
    With Range("D2")
        .Formula = "=(C4+B4)/LOG10(A4) + 12"
        .AutoFill Destination:=Range("D2:D3")
    End With
End Sub

Note the arguments will auto-adjust in the fill-down:

enter image description here

Upvotes: 1

Related Questions