copy an excel formula to a new range-CodePudding

I have an excel worksheet with the following data:
A1: 1
B1: = A1 2
A5: 2

I tried to create a macro to copy the formula from B1 to B5

Sub test()
    MsgBox Cells(1, 1)
    Cells(5, 2) = Cells(1, 2).Formula
End Sub

But this macro does not change the formula cell value. Meaning, the macro copies the formula = A1 2 but I'd like it to be = A5 2. Is there an easy way to do this?

CodePudding user response：

You want the .Formula2R1C1 property as follows:

Sub copy_formula()

  Range("b5").Formula2R1C1 = Range("b1").Formula2R1C1

End Sub

Sub copy_formula()

  Cells(5, 2).Formula2R1C1 = Cells(1, 2).Formula2R1C1

End Sub