Sum a dynamic range of cells based on another cells value
Hi,
Trying to sum a range of cells across Excel spreadsheets and using VBA to do it.
The length of the range changes in each cell but the range will always be the same, i.e. using column A - values starting in row 2. Formula in B2 is sum(A2:A3002), B3=sum(A3:A3003) and so on if range was 3000 cells - this can change based on a value entered elsewhere (if this is the easiest way to do it). This formula would continue until the first cell found is blank (i.e. the end of the range).
Also if it is possible i would simply like to return the values rather than the formulas as this task in itself makes my Excel fall over....
Something about turning off screen refresh i have heard....
Thanks in advance for your help.
place the cursor and run the macro
Hello,
Just place the cursor on the first cell of the sum in this example you should select cell A2 or A3 and run this macro:
Option Explicit
Sub SumSpecificRange()
'
' SumSpecificRange Macro
Dim sum As Variant
sum = 0
Do Until ActiveCell.Value = ""
sum = sum + ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
Dim findrange As Variant
findrange = sum
Do Until findrange = 0
findrange = findrange - ActiveCell.Value
ActiveCell.Offset(-1, 0).Select
Loop
ActiveCell.Offset(1, 1).Value = sum
End Sub
It will sum until the first empty cell and will print the value on cell b2 or b3 depending if you are running for based on cell a2 or a3.
I hope it helps.
Cátia Santos