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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:

Mike2

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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) & ")"
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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:

Mike2

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

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

Well-known Member
Joined
Oct 27, 2005
Messages
21,345
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
Perhaps.
Code:
cel.FormulaR1C1 = "=ROUND(SUM(R2C:R[-1]C),2)"
 

Forum statistics

Threads
1,171,065
Messages
5,873,583
Members
432,985
Latest member
leahw

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
Top