CODEWITHSUNDEEP
excelvba
Frank
Frank

Reputation: 1196

copy an excel formula to a new range

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?

Upvotes: 1

Views: 211

Answers (1)

Gove
Gove

Reputation: 1794

You want the .Formula2R1C1 property as follows:

Sub copy_formula()

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

End Sub

or

Sub copy_formula()

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

End Sub

Upvotes: 1

Related Questions