Cummulative Totals


G

Guest

Hi all,

I use the code below to produce cummulative totals for a given month (Days
1-31)

Issue:
If i use this code to produce a row of data, for a mid-month report,
specifically a graph, where i run out of data, the unknown days contain the
last known value.

Question:
How can tell the code that if there is no value for the date, display a '0',
otherwise, calculate the cummunlative value for the day in question.



Code:
Range("C58") = Range("C11")
Range("D58") = Application.WorksheetFunction.Sum(Range("C58,D11"))
Range("E58") = Application.WorksheetFunction.Sum(Range("D58,E11"))
Range("F58") = Application.WorksheetFunction.Sum(Range("E58,F11"))
Range("G58") = Application.WorksheetFunction.Sum(Range("F58,G11"))
Range("H58") = Application.WorksheetFunction.Sum(Range("G58,H11"))

Range("I58") = Application.WorksheetFunction.Sum(Range("H58,I11"))

Range("J58") = Application.WorksheetFunction.Sum(Range("I58,J11"))
Range("K58") = Application.WorksheetFunction.Sum(Range("J58,K11"))
Range("L58") = Application.WorksheetFunction.Sum(Range("K58,L11"))
Range("M58") = Application.WorksheetFunction.Sum(Range("L58,M11"))

Range("N58") = Application.WorksheetFunction.Sum(Range("M58,N11"))
Range("O58") = Application.WorksheetFunction.Sum(Range("N58,O11"))
Range("P58") = Application.WorksheetFunction.Sum(Range("O58,P11"))
Range("Q58") = Application.WorksheetFunction.Sum(Range("P58,Q11"))
Range("R58") = Application.WorksheetFunction.Sum(Range("Q58,R11"))

Range("S58") = Application.WorksheetFunction.Sum(Range("R58,S11"))
Range("T58") = Application.WorksheetFunction.Sum(Range("S58,T11"))
Range("U58") = Application.WorksheetFunction.Sum(Range("T58,U11"))
Range("W58") = Application.WorksheetFunction.Sum(Range("V58,W11"))

Range("X58") = Application.WorksheetFunction.Sum(Range("W58,X11"))
Range("Y58") = Application.WorksheetFunction.Sum(Range("X58,Y11"))
Range("Z58") = Application.WorksheetFunction.Sum(Range("Y58,Z11"))
Range("AA58") = Application.WorksheetFunction.Sum(Range("Z58,AA11"))
Range("AB58") = Application.WorksheetFunction.Sum(Range("AA58,AB11"))

Range("AC58") = Application.WorksheetFunction.Sum(Range("AB58,AC11"))
Range("AD58") = Application.WorksheetFunction.Sum(Range("AC58,AD11"))
Range("AE58") = Application.WorksheetFunction.Sum(Range("AD58,AE11"))
Range("AF58") = Application.WorksheetFunction.Sum(Range("AE58,AF11"))
Range("AG58") = Application.WorksheetFunction.Sum(Range("AF58,AG11"))
 
Ad

Advertisements

D

Don Guillett

This should make your formulas a bit more efficiently.

Sub df()
'Range("C58") = Range("C11")
Cells(58, 3) = Cells(11, 3)
For i = 4 To 34
Cells(58, i) = Application.Sum(Range(Cells(58, i), Cells(11, i + 1)))
Next
End Sub

Not quite sure about the rest but maybe you can use an IF
sub df1()
Cells(58, 3) = Cells(11, 3)
for i=4 to 34
if cells(i,11)>0 then 'or cells(i,58)
Cells(58, i) = Application.Sum(Range(Cells(58, i), Cells(11, i + 1)))
end if
next
end sub
 
G

Guest

Hi there,
i am a bit unsure how to implement this code. How can 'call' this on my
sheet to make it work?
 
D

Don Guillett

Just try it. If it doesn't do it for you don't save.....
I could probably figure out the rest if I saw your workbook. Send to my
address below, if desired.
 
Ad

Advertisements

G

Guest

Hi there,
Well...I tried it, and it worked, however, the numbers were not correct.
I will sent the workbook along
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

efficiency 3

Top