Sum

  • Thread starter Thread starter Bishop
  • Start date Start date
B

Bishop

I'm trying to sum all the values in a column and assign it to a variable.
This what I tried:

With Worksheets("Movies")

Paid = .Sum("I:I")

but Sum apparently doesn't take column references. Is there a way around
this?
 
Can you work with this?

Option Explicit
Sub dynamicsumming()
Dim lastrow As Long
Dim ws As Worksheet

Set ws = Worksheets("Movies")
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
Range("G1") = "=sum(I1:I" & lastrow & ")"
End Sub

HTH,
Ryan---
 
This essentially does the same thing. Feeds a column reference to the Sum
function. According to the help file you need to give it individual
arguments:

expression.Sum(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10,
Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21,
Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

expression A variable that represents a WorksheetFunction object.

Do I need to write a loop? And I will definitely have over 30 arguments so
what do I do once I reach that limit?
 
Try it this way...

Paid = WorksheetFunction.Sum(Worksheets("Movies").Columns("I"))

You can put the Worksheets("Movies") reference in a With statement like your
original post showed if you will be needing to reference it elsewhere in
your code.
 
Back
Top