0

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

Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
JayJayAbrams
  • 195
  • 1
  • 16
  • Concerning the log math formulas - `Debug.Print Math.Log(10)` Or even better - check https://stackoverflow.com/a/31618564/5448626 – Vityata Nov 08 '17 at 16:06

1 Answers1

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:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Perfect! And how do I change in formula C4, B4 and A4 to row below them in followin run? @garys-student – JayJayAbrams Nov 08 '17 at 16:25
  • @J See the picture in the answer...........the formula automatically adjusts during the fill-down process. – Gary's Student Nov 08 '17 at 16:28
  • @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. – JayJayAbrams Nov 08 '17 at 16:43