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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bishop" wrote:

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?

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?

"ryguy7272" wrote:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bishop" wrote:

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?

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.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
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?

"ryguy7272" wrote:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"Bishop" wrote:

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?