Replace formula in range of cells (with/without if then statement)

[bjprent]

Hi,

I just started out with VBA so I am new to all of it. So my question is how can I replace a formula in a column range.

So I want to change the formula's in the cells M4:M20. The current formula in M4 is really simple; =SUM(C4:E4;G4:H4;J4:K4) now I want to replace this formula with another one namely; =SUM(C4:F4;H4:I4). Can someone show me the syntax for this macro? I know I can change it manually but I would like to learn how to build this in VBA so I can use it as a macro.

And if you are awesome with VBA I would like to know how to use it in an if then syntax like below. So if F2 has a value of Actual Q3 then the old formula is changed into the new one.

Private Sub CommandButton1_Click()

If Range("F2").Value = "Actual Q3" Then
Range("G:G, H:H").Replace ?????[don't know if this is possible]
End If

End Sub


Thanks in advance ,

Bart

[rcm]

This macro inserts the formula in m4 according to the value in f2. if the range M4:m20 needs to be changed then it can be embedded into a loop as shown in the second part. Only one of the sections is needed.

Please Login or Register  to view this content.

[bjprent]

Awesome dude works like a charm. Just needed to add the End If in the second section but that was easy to figure out!!

Tnx

[rcm]

Glad you caught my mistake. Happy to have been able to help. Please mark your thread as SOLVED