efficiency

[Guest]

I use the code below to populate a row of data, (C58 to AG58). Basically
what i am trying to do is create a cumulative total for a specific month.

Question:
1. How can i make this code more efficient
2. How can i account for mid month where there is no value. I want the
un-known values to equal '0'

Can anyone help me out?

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"))

 

[Gary Keramidas]

carlee:

not sure what you're asking, but i'll take a stab. do you get an error or
something when you sum half of the month? if these don't work, post back some
more info.

one way to simplify if you want a formula:
Sub test()
Dim i As Long
For i = 4 To 30
Cells(58, i).Formula = "=sum(" & Cells(58, i - 1).Address(0, 0) & "," & _
Cells(11, i).Address(0, 0) & ")"
Next
End Sub

or if you want the sum:

Sub test2()
Dim i As Long
For i = 4 To 30
Cells(58, i).Value = Application.WorksheetFunction.Sum(Range(Cells(58, i - 1), _
Cells(11, i)))
Next
End Sub

 

[Otto Moehrbach]

Carlee
This little macro will do what you wrote, but I'm sure you want to do
more than just the one row. Also I don't know what you mean in your second
question. HTH Otto
Sub FillIn()
Dim c As Long
Range("C58") = Range("C11")
For c = 4 To 33
Cells(58, c) = Cells(58, c - 1) + Cells(11, c)
Next c
End Sub

 

[Bob Phillips]

Range("C58") = Range("C11")
Range("D58").Resize(1, 28).Formula = "=SUM(RC[-1],R11C)"
Range("D58").Resize(1, 28).Value = Range("D58").Resize(1, 28).Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)