How do I sum a column of data in a macro?

Assume I have a worksheet and cells C1 through C20 have numbers. How can I
get the sum of those numbers without a loop?

I tried
Total = Sum(Range("C1").End(xlDown))

But got an error that Sum is not defined as a funtion.

DG

On Oct 5, 6:37 pm, "DG" wrote:
How do I sum a column of data in a macro?

Assume I have a worksheet and cells C1 through C20 have numbers. How can I
get the sum of those numbers without a loop?

I tried
Total = Sum(Range("C1").End(xlDown))

But got an error that Sum is not defined as a funtion.

DG

Try
Total=WorksheetFunction.Sum(Range("C1",Range("C1") .End(xlDown))

Try

Total = Application.Sum(Range("C1").End(xlDown))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"DG" wrote in message
...
How do I sum a column of data in a macro?

Assume I have a worksheet and cells C1 through C20 have numbers. How can I
get the sum of those numbers without a loop?

I tried
Total = Sum(Range("C1").End(xlDown))

But got an error that Sum is not defined as a funtion.

DG

Sweet.... Works like a charm.

Thanks

DG

wrote in message
oups.com...
On Oct 5, 6:37 pm, "DG" wrote:
How do I sum a column of data in a macro?

Assume I have a worksheet and cells C1 through C20 have numbers. How can
I
get the sum of those numbers without a loop?

I tried
Total = Sum(Range("C1").End(xlDown))

But got an error that Sum is not defined as a funtion.

DG

Try
Total=WorksheetFunction.Sum(Range("C1",Range("C1") .End(xlDown))

Okay, now I have a question for the experts.
What are the pros/cons/differences between:

Total=WorksheetFunction.Sum(...)
and
Total=Application.Sum(...)

Thanks, I'm just trying to learn more about VBA programming.
-pb

There is no difference.

"cubbybear3" wrote in message
ups.com...
Okay, now I have a question for the experts.
What are the pros/cons/differences between:

Total=WorksheetFunction.Sum(...)
and
Total=Application.Sum(...)

Thanks, I'm just trying to learn more about VBA programming.
-pb

There is no difference.

Not true; there is a difference when it comes to error handling. If you
omit the "WorksheetFunction", you can trap the error in a Variant and test
that with IsError. If you include "WorksheetFunction", you must trap a
run-time error with an On Error statement.

Sub AAA()
Dim V As Variant
V = Application.Sum(Range("A1:A3"))
If IsError(V) = True Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If

On Error Resume Next
Err.Clear
V = Application.WorksheetFunction.Sum(Range("A1:A3"))
If Err.Number < 0 Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Zone" wrote in message
...
There is no difference.

"cubbybear3" wrote in message
ups.com...
Okay, now I have a question for the experts.
What are the pros/cons/differences between:

Total=WorksheetFunction.Sum(...)
and
Total=Application.Sum(...)

Thanks, I'm just trying to learn more about VBA programming.
-pb

I stand corrected!

"Chip Pearson" wrote in message
...
There is no difference.

Not true; there is a difference when it comes to error handling. If you
omit the "WorksheetFunction", you can trap the error in a Variant and test
that with IsError. If you include "WorksheetFunction", you must trap a
run-time error with an On Error statement.

Sub AAA()
Dim V As Variant
V = Application.Sum(Range("A1:A3"))
If IsError(V) = True Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If

On Error Resume Next
Err.Clear
V = Application.WorksheetFunction.Sum(Range("A1:A3"))
If Err.Number < 0 Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Zone" wrote in message
...
There is no difference.

"cubbybear3" wrote in message
ups.com...
Okay, now I have a question for the experts.
What are the pros/cons/differences between:

Total=WorksheetFunction.Sum(...)
and
Total=Application.Sum(...)

Thanks, I'm just trying to learn more about VBA programming.
-pb

Not true; there is a difference when it comes to error handling. If you
omit the "WorksheetFunction", you can trap the error in a Variant and test
that with IsError. If you include "WorksheetFunction", you must trap a
run-time error with an On Error statement.

Sub AAA()
Dim V As Variant
V = Application.Sum(Range("A1:A3"))
If IsError(V) = True Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If

On Error Resume Next
Err.Clear
V = Application.WorksheetFunction.Sum(Range("A1:A3"))
If Err.Number < 0 Then
Debug.Print "ERROR USING SUM"
Else
Debug.Print CStr(V)
End If
End Sub

Thanks Chip. BTW, the newsletters are very educational.
-pb