| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop and sum
Jenna,
Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
Jenna,
Your summed range at the end: SUM($C$1:C2) is wrong. It should be column D: =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"") HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii I would prefer the formula but i'think its a dead end here's my formula =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"") the problem is after the IF is true i want the formula to start from the nest row is possible let me know. thanks "Bernie Deitrick" wrote: Jenna, Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
bernie The only problem is have with the formula is after the test is true i want that value in column D instead of the total thanks so far "Bernie Deitrick" wrote: Jenna, Your summed range at the end: SUM($C$1:C2) is wrong. It should be column D: =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"") HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii I would prefer the formula but i'think its a dead end here's my formula =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"") the problem is after the IF is true i want the formula to start from the nest row is possible let me know. thanks "Bernie Deitrick" wrote: Jenna, Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
Jenna,
So, if you have a column filled with just the number 101, so that by row 36 (the 35th value) you have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or 35? And do you want to start your sum over with 0, or with the amount that the sum was over 3,500? And what if the sum is exactly 3,500? HTH, Bernie MS Excel MVP "jenna" wrote in message ... bernie The only problem is have with the formula is after the test is true i want that value in column D instead of the total thanks so far "Bernie Deitrick" wrote: Jenna, Your summed range at the end: SUM($C$1:C2) is wrong. It should be column D: =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"") HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii I would prefer the formula but i'think its a dead end here's my formula =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"") the problem is after the IF is true i want the formula to start from the nest row is possible let me know. thanks "Bernie Deitrick" wrote: Jenna, Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
Hii Bernie
I want 101 in column D and start at 0 Thanks you so much "Bernie Deitrick" wrote: Jenna, So, if you have a column filled with just the number 101, so that by row 36 (the 35th value) you have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or 35? And do you want to start your sum over with 0, or with the amount that the sum was over 3,500? And what if the sum is exactly 3,500? HTH, Bernie MS Excel MVP "jenna" wrote in message ... bernie The only problem is have with the formula is after the test is true i want that value in column D instead of the total thanks so far "Bernie Deitrick" wrote: Jenna, Your summed range at the end: SUM($C$1:C2) is wrong. It should be column D: =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"") HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii I would prefer the formula but i'think its a dead end here's my formula =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"") the problem is after the IF is true i want the formula to start from the nest row is possible let me know. thanks "Bernie Deitrick" wrote: Jenna, Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
Jenna,
The easiest thing would be to use column E, with a formula like this (written for E3): =IF(D3<"",C3,"") Copy down to match the formulas in column D, then hide column D. HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii Bernie I want 101 in column D and start at 0 Thanks you so much "Bernie Deitrick" wrote: Jenna, So, if you have a column filled with just the number 101, so that by row 36 (the 35th value) you have 3,535. What do you want in column D? 101, or 3,500, or 3,535, or 35? And do you want to start your sum over with 0, or with the amount that the sum was over 3,500? And what if the sum is exactly 3,500? HTH, Bernie MS Excel MVP "jenna" wrote in message ... bernie The only problem is have with the formula is after the test is true i want that value in column D instead of the total thanks so far "Bernie Deitrick" wrote: Jenna, Your summed range at the end: SUM($C$1:C2) is wrong. It should be column D: =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($D$1:D2),"") HTH, Bernie MS Excel MVP "jenna" wrote in message ... Hii I would prefer the formula but i'think its a dead end here's my formula =IF(SUM($C$1:C3)-SUM($D$1:D2) 3500,SUM($C$1:C3)-SUM($C$1:C2),"") the problem is after the IF is true i want the formula to start from the nest row is possible let me know. thanks "Bernie Deitrick" wrote: Jenna, Put this formula into cell D2, and copy down to match your column C: =IF(SUM($C$1:C2)-SUM($D$1:D1) 3500,SUM($C$1:C2)-SUM($D$1:D1),"") Otherwise, you could use this macro: Sub SumC() Dim myC As Range Dim mySum As Double mySum = 0 For Each myC In Intersect(Range("C2:C65536"), _ ActiveSheet.UsedRange) mySum = mySum + myC.Value If mySum 3500 Then myC(1, 2).Value = mySum mySum = 0 End If Next myC End Sub HTH, Bernie MS Excel MVP "jenna" wrote in message ... Please help i need a procedure to loop down column c and the first row where the sum is greater than 3500 put that amount in the adjacent column, then repeat from the next row down. thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and sum
thanks bernie
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Loop Function unable to loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
| HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Linear Mode
