Sum in VBA

[Jeff]

Hello,

How can I sum up the column I from row 4 to the next empty row in column I?
Thanks,

 

[Dave]

Try this (works for rows 4 through 20,000):

Sub test()
For Each c In Range("I4:I20000")

If Trim(c) <> "" Then
mysum = mysum + c
Else
Exit For
End If

Next c
MsgBox (mysum)
End Sub

 

[Bob Phillips]

mySum = worksheetfunction.sum(Range("I4").End(xlDown))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andy Pope]

Hi,

Msgbox application.WorksheetFunction.Sum( _
Range(range("I4"),range("I4").End(xlDown)))

Cheers
Andy

 

[Bernie Deitrick]

Jeff,

Sub test()

Dim mySum As Double

If Not IsEmpty(Range("I5")) Then
mySum = Application.Sum(Range(Range("I4"), Range("I4").End(xlDown)))
Else
mySum = Range("I4").Value
End If

MsgBox mySum

End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Andy,

This will give the incorrect value if cell I5 is blank.

HTH,
Bernie
MS Excel MVP

 

[Dave]

This a neat way to add functions to your files. On the sheet use formula:
=MySum(I4:I200). Place the below code into a module. Note: it will produce
an error if you have a cell in column I (before the next blank cell)that is
non-numeric. This can be easily reconciled with a little extra code.

Function MySum(myRange)
Dim c, MyValue

For Each c In myRange

If Trim(c) <> "" Then
MyValue = MyValue + c
Else
Exit For
End If

Next c
MySum = MyValue
End Function

 

[Andy Pope]

Hi Bernie,

Good catch! I never noticed that.

Cheers
Andy

Andy,

This will give the incorrect value if cell I5 is blank.

HTH,
Bernie
MS Excel MVP