[VBA] SUM RANGE OF VALUES

[Eddie_SP]

Hello.

First of all, thank you Dave for advising me about the file uploaded. =)

My question is:

How do I sum range ("D17") until (last value of the column)?

I have the following code:



Private Sub CommandButton1_Click()

Dim Coluna As Long
Dim i As Integer
Dim W As Integer
Dim Plan As Worksheet
Set Plan = Worksheets(1)

If Trim(Me.TextBox1.Value) = "" Then
MsgBox "Digite um valor."
Exit Sub
End If

Range("B17").Select

i = 0

While (ActiveSheet.Cells(17 + i, 2) <> 0)
i = i + 1
Wend
If Range("C17") = "" Then
Range("C17") = Me.TextBox1.Value
Range("C17").NumberFormat = "_($
#,##0.00_);_((#,##0.00);_(""-""??_);_(@_)"
Else
Range(Cells(17 + i - 1, 2), Cells(17 + i - 1, 4)).Select
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Range(Cells(17 + i, 2), Cells(17 + i, 4)).Select
Selection.Copy
Range(Cells(17 + i - 1, 2), Cells(17 + i - 1, 4)).Select
ActiveSheet.Paste
Cells(17 + i, 2).Select
Application.CutCopyMode = False
Cells(17 + i, 2).Value = i + 1
Cells(17 + i, 3).Value = Me.TextBox1.Value
Cells(17 + i + 1, 4).Select
End If

W = Cells(17 + i, 4)
'I NEED THE ROUTINE TO SUM FROM "D17" 'TILL LAST VALUE.
Set MyRange = ActiveSheet.Range(Cells(17, 4), Cells(17 + i - 1, 4))
W = Application.WorksheetFunction.Sum(MyRange)

Me.TextBox1.Value = Empty
Me.TextBox1.SetFocus

End Sub



If someone wants to check the file, I have uploaded. There's nothing bad in
it...

Thank you !

Eddie - Sao Paulo - Brasil

 

[arjen van...]

When summing a column it can be easier to use an array along with the sum
function. something along the lines of:

Dim arrD As Variant
With Sheets("Sheet1")
arrD = .Range(.Range("D17"), .Range("D17").End(xlDown))
End With

Dim sumD As Long
sumD = WorksheetFunction.Sum(arrD)

The array is always declared as Variant, the sum will depend on your data
(probably Long or Double).

 

[arjen van...]

I left a small piece out (.Value) - to make sure it works properly:

arrD = .Range(.Range("D17"), .Range("D17").End(xlDown)).Value

 

[Eddie_SP]

GREAT !

THANK YOU SIR !!!