Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] SUM RANGE OF VALUES
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] SUM RANGE OF VALUES
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] SUM RANGE OF VALUES
I left a small piece out (.Value) - to make sure it works properly:
arrD = .Range(.Range("D17"), .Range("D17").End(xlDown)).Value |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] SUM RANGE OF VALUES
GREAT !
THANK YOU SIR !!! "arjen van..." wrote: I left a small piece out (.Value) - to make sure it works properly: arrD = .Range(.Range("D17"), .Range("D17").End(xlDown)).Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
There must be a way!!!--set cells' values equal to calculated values in another range | Excel Programming | |||
Search/Filter to find values in another range based on two cell values | Excel Programming |