How to keep SUM formula in cell after VBA

[DarkSilence1979]

Hello everyone!

First time post here and I am hoping someone will be able to answer a couple of issues I have been having with just starting out with VBA on excel. I have a spreadsheet that will have new info every week so the columns will stay the same, but the number of rows will vary. After a lot of searching on google, I was able to figure out how to sum the columns with varying rows by using the following VBA:

Dim myRangeD
Dim myRangeE
Dim myRangeF


Range("G1").Select
ActiveCell.FormulaR1C1 = "40"
Cells.Select
Cells.EntireColumn.AutoFit
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "General"

Set endRE = ActiveSheet.Range("E2").End(xlDown)
Set myRangeE = ActiveSheet.Range("E2", endRE)
endRE.Offset(2, 0) = WorksheetFunction.Sum(myRangeE)

Set endRF = ActiveSheet.Range("F2").End(xlDown)
Set myRangeF = ActiveSheet.Range("F2", endRF)
endRF.Offset(2, 0) = WorksheetFunction.Sum(myRangeF)

Set endRD = ActiveSheet.Range("D2").End(xlDown)
Set myRangeD = ActiveSheet.Range("D2", endRD)
endRD.Offset(2, 0) = WorksheetFunction.Sum(myRangeD)

It works great, the only problem is after the macro is ran, the WorksheetFunction.Sum(myRangeE), (myRangeF), and (myRangeD) all input a value into the cell. I need it to keep it as a formula since this worksheet is dynamic and users change values in the columns and are expecting a new calculated value in these cells. How do I force VBA to keep the formula instead of calculating the value in the background? Thanks to everyone for their time.

Ryan

 

[mohadin]

Hi
try to change 2 line to

endRF.Offset(2, 0).Formula = "=Sum(""D2"", endRD)"
endRD.Offset(2, 0).Formula = "=Sum(""E2"", endRE)"

.
.

 

[mohadin]

Sub rrr()


    Range("G1").Select
    ActiveCell.FormulaR1C1 = "40"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "General"


    Set endRE = ActiveSheet.Range("E2").End(xlDown)
    endRE.Offset(2, 0).Formula = "=Sum(""E2"", endRE)"


    Set endRF = ActiveSheet.Range("F2").End(xlDown)
    endRF.Offset(2, 0).Formula = "=Sum(""F2"", endRF)"


    Set endRD = ActiveSheet.Range("D2").End(xlDown)
    endRD.Offset(2, 0).Formula = "=Sum(""D2"", endRD)"


End Sub

 

[Fluff]

If all columns are the same height, you could use

Sub DarkSilence()
    Range("G1").Value = "40"
    Cells.EntireColumn.AutoFit
    Range("D:D").NumberFormat = "General"

    Range("D" & Rows.Count).End(xlUp).Offset(2).Resize(, 3).FormulaR1C1 = "=sum(r2c:r[-2]c)"
End Sub

 

[DarkSilence1979]

Thanks everyone. I will give these a try and let everyone know if they worked.

 

[DarkSilence1979]

Sub rrr()


    Range("G1").Select
    ActiveCell.FormulaR1C1 = "40"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "General"


    Set endRE = ActiveSheet.Range("E2").End(xlDown)
    endRE.Offset(2, 0).Formula = "=Sum(""E2"", endRE)"


    Set endRF = ActiveSheet.Range("F2").End(xlDown)
    endRF.Offset(2, 0).Formula = "=Sum(""F2"", endRF)"


    Set endRD = ActiveSheet.Range("D2").End(xlDown)
    endRD.Offset(2, 0).Formula = "=Sum(""D2"", endRD)"


End Sub

Mohadin,
Thank you but when I use your code, my resulting cell only gives me a #VALUE . Somehow it is not calculating the value.

 

[DarkSilence1979]

If all columns are the same height, you could use

Sub DarkSilence()
    Range("G1").Value = "40"
    Cells.EntireColumn.AutoFit
    Range("D:D").NumberFormat = "General"

    Range("D" & Rows.Count).End(xlUp).Offset(2).Resize(, 3).FormulaR1C1 = "=sum(r2c:r[-2]c)"
End Sub

Fluff,
Thank you. Your code worked great. If you do not mind, could you explain the code? Just getting into VBA and want to understand it more. Thank you.

 

[Fluff]

[COLOR=#ff0000]Range("D" & Rows.Count).End(xlUp).Offset(2)[/COLOR].[COLOR=#0000ff]Resize(, 3)[/COLOR].FormulaR1C1 = "=sum(r2c:r[-2]c)"

The part in red finds the last cell with a value/formula in column D & then comes down 2 cells.
The part in blue then resizes the range to 3 columns wide making Dx:Fx (where x is whatever row the part in red came to).
It then puts the formula into those cells.

The R1C1 notation used in the formula means
row2 this column and row-2 this column
so for a formula in D37 it becomes D2:D35

 

[DarkSilence1979]

Thank you for the explanation Fluff. I will definitely put this to good use.

 

[Fluff]

My pleasure & thanks for the feedback