Mike2

New Member
Joined
Jan 5, 2019
Messages
41
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...
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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?
Code:
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
 
Last edited:
Upvote 0
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) & ")"
 
Upvote 0
How about
Code:
    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
Code:
    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) & ")"
 
Upvote 0
Why not use R1C1 notation?
Code:
cel.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

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

=SUM(D$2:D19)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
Try using


Code:
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
 
Last edited:
Upvote 0
Perhaps.
Code:
cel.FormulaR1C1 = "=ROUND(SUM(R2C:R[-1]C),2)"
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top