Put a Formula In A Cell

[MikeG]

Could anyone tell me how to put a formula in a cell using VBA?

So, for example, I have numeric values in cells B3, B5, and B7. I want cell B9, which starts off as blank, to be given the formula: SUM(B3:B7).

(Cells B4 and B6 are blank).

However, I would like the sum formula in cell B9 to be relative, so that if I delete what is now column A, the sum will still work without me having to change the macro command.

Thanks,

MikeG

 

[VoG]

Try

Range("B9").Formula = "=SUM(B3:B7)"

 

[MikeG]

Thanks Peter.

That's great.

One enhancement I am looking for...

If I run the macro command, and then delete column A the SUM formula is now in A and refers to the cells in A - i.e. it is now =Sum(A3:A7), which is what I need.

However, if I rerun the macro, the command still puts the formula in "B9", because, of course, that is hard-coded, and the SUM formula still points to B3:B7, which are now blank because the values have since moved to column A.

So is there a way of making the command relative? I will tell the macro where the cell to put the formula goes using variables x and y, which will be updated if a column is deleted.

I think what I am looking for is something like:

Cells(x, y).Formula = "=SUM(B3:B7)"

But instead of the hardcoded B3:B7, is it something like "SUM(R-6C,R-2C)"?

Thanks,

Mike

 

[VoG]

You can do that like this but it will still add the same formula

Range("B9").FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)"

 

[MikeG]

You can do that like this but it will still add the same formula

Range("B9").FormulaR1C1 = "=SUM(R[-6]C:R[-2]C)"

Thanks. I see what you mean.

Is there a way to use a string type of formula?

So in the macro, define:

Column_to_Sum = "B"

Range(Column_To_Sum&"9").Formula = "=SUM(Column_To_Sum&"3,"&Column_To_Sum&"7)"

Mike

 

[VoG]

Yes but you''ll end up with the same formula

Sub test()

Dim Column_To_Sum  As String
Column_To_Sum = "B"
Range(Column_To_Sum & 9).Formula = "=SUM(" & Column_To_Sum & 3 & ":" & Column_To_Sum & 7 & ")"
End Sub

 

[MikeG]

Yes but you''ll end up with the same formula

Sub test()

Dim Column_To_Sum  As String
Column_To_Sum = "B"
Range(Column_To_Sum & 9).Formula = "=SUM(" & Column_To_Sum & 3 & ":" & Column_To_Sum & 7 & ")"
End Sub

Thanks Peter. That helps - I'll use a Cell("Col","Formula_Cell") in the worksheet to track the column of the formula cell, read that into the macro, and set the Column_To_Sum variable accordingly.

Mike