Dynamic AutoSum

[Mike2]

Hello,

I am looking to do a Dynamic AutoSum function in VBA.

Here is my code:

Rows(1).Find(what:="Qty $", lookat:=xlWhole).Select
LstRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
Cells(LstRow, ActiveCell.Column).Formula = "=SUM(ActiveCell:ActiveCell.Column" & LstRow - 1 & ")"

The execution gave me a #NAME ? error at the end of the row.

But let say "Qty $" is in Column B, and when I enter the formula as
"=SUM(B1:B" & LstRow - 1 & ")" in replacement to the one above, then it works find.

Is there a way to not specify the Range and use Dynamic Range instead?

Thank you for your expert advise in advance...

 

[Yongle]

Do you want to sum from row 1
- row 1 contains the header
- amended to row 2 in code below

Is this what you want?

Sub TestFormula()
    Dim cel As Range, cel2 As Range, LstRow As Long
    Set cel = Rows(1).Find(what:="Qty $", lookat:=xlWhole)
    LstRow = Cells(Rows.Count, cel.Column).End(xlUp).Row
    Set cel = cel.Offset(1)
    Set cel2 = Cells(LstRow, cel.Column)

    Cells(LstRow + 1, cel.Column).Formula = "=SUM(" & cel.Address(0, 0) & ":" & cel2.Address(0, 0) & ")"

End Sub

 

[Mike2]

Hi Yongle,

Thank you for your help...your suggested code works great !!

With your suggestion, I have inco-operated the Address function into my code and it works !!

My final code is:

Rows(1).Find(what:="Qty $", lookat:=xlWhole).offset(1).Select
LstRow=Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row+1
Cells(LstRow, ActiveCell.Column).Formula="=SUM(" & ActiveCell.Address(0,0) & ":" & Cells(LstRow - 1, ActiveCell.Column).Address(0,0) & ")"

 

[Yongle]

How about

    Cells(Rows.Count, Rows(1).Find(what:="Qty $", lookat:=xlWhole).Column).End(xlUp).Offset(1).Select
    ActiveCell.Formula = "=sum(" & Range(Cells(2, ActiveCell.Column), ActiveCell.Offset(-1)).Address(0, 0) & ")"

Is there a reason for selecting cells in your code?
- not efficient (code runs much slower)
- not required by VBA

Alternative without selecting any cells

    Dim cel As Range
    Set cel = Cells(Rows.Count, Rows(1).Find(what:="Qty $", lookat:=xlWhole).Column).End(xlUp).Offset(1)
    cel.Formula = "=sum(" & Range(Cells(2, cel.Column), cel.Offset(-1)).Address(0, 0) & ")"

 

[Norie]

Why not use R1C1 notation?

cel.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

If cel was, say, D20 this would put this formula in cel:

=SUM(D$2:D19)

 

[Yongle]

Thanks @Norie
Very concise

 

[Mike2]

Thank you @ Norie and @Yongle for your suggestion.

There is no particular reason to selecting Cells in the coding.
I will try to use whichever logic comes to mind first to get the code working, then work around the improvement/enhancement after
to make the process run more efficiently. After all, i am still a novice in VBA programming.

 

[Mike2]

Hi Yongle,

Suppose if i want to add in the round function to the autosum result to 2 decimal places, how would you suggest the coding to be?

Thanks,

 

[Michael M]

Try using

Sub TestFormula()
    Dim cel As Range, cel2 As Range, LstRow As Long
    Set cel = Rows(1).Find(what:="Qty $", lookat:=xlWhole)
    LstRow = Cells(Rows.Count, cel.Column).End(xlUp).Row
    Set cel = cel.Offset(1)
    Set cel2 = Cells(LstRow, cel.Column)

    Cells(LstRow + 1, cel.Column).Formula = "=[color=red]ROUND[/color](SUM(" & cel.Address(0, 0) & ":" & cel2.Address(0, 0) & ")[color=red],2[/color])"
End Sub

 

[Norie]

Perhaps.

cel.FormulaR1C1 = "=ROUND(SUM(R2C:R[-1]C),2)"