Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > efficiency
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 155
Default efficiency
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 "))
--
Carlee
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default efficiency
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

--


Gary


"Carlee" wrote in message
...
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 "))
--
Carlee


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,090
Default efficiency
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
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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

--


Gary


"Carlee" wrote in message
...
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 "))
--
Carlee




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default efficiency
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)

"Carlee" wrote in message
...
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 "))
--
Carlee


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Equation Efficiency? Ithaca Excel Worksheet Functions 1 April 29th 09 01:20 AM
Range efficiency Steven Excel Worksheet Functions 0 December 9th 05 02:15 PM
VBA Efficiency Question Brian Excel Programming 4 March 26th 05 04:10 PM
VB Efficiency: Inserting a Row Tippy[_3_] Excel Programming 8 May 26th 04 08:34 PM
.select efficiency Lawlera Excel Programming 4 February 4th 04 02:38 PM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement