Sum a column of data

[DG]

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

 

[stjori]

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

 

[Bob Phillips]

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]

Sweet.... Works like a charm.

Thanks

DG

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

 

[cubbybear3]

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

 

[Zone]

There is no difference.

 

[Chip Pearson]

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]

I stand corrected!

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)

 

[cubbybear3]

Not true; there is a difference when it comes to error handling. If you
Thanks Chip. BTW, the newsletters are very educational.
-pb