excel vba | change absolute cells in loop

Question

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 :<

Concerning the log math formulas - Debug.Print Math.Log(10) Or even better - check stackoverflow.com/a/31618564/5448626 — Vityata
– Vityata, Commented Nov 8, 2017 at 16:06

Gary's Student · Accepted Answer · 2017-11-08 16:11:42Z

1

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:

answered Nov 8, 2017 at 16:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JayJayAbrams Over a year ago

Perfect! And how do I change in formula C4, B4 and A4 to row below them in followin run? @garys-student

Gary's Student Over a year ago

@J See the picture in the answer...........the formula automatically adjusts during the fill-down process.

JayJayAbrams Over a year ago

@garys-student my bad, I wanted to apply it to my example w/ absolute cell =ref!$C$2*4 or better this one =($С$4+$B$4)/LOG10($A$4) + 12. As it can be seen from the beginning For Each cycle In Sheets("ref").Range("A2:A4") there are 3 values in cycle so there will be 3 runs. What I what to do is to for each run automatically move absolute cells a row down or column left or wherever.

Collectives™ on Stack Overflow

excel vba | change absolute cells in loop

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related