Sum or subtotal at a specified amount

[ChrisV]

This is my first time using this forum...

I am trying to make a schedule that uses the dollar amounts of various jobs to schedule a certain amount of work in a week. For example if I have a hundred jobs that are worth various amounts of money each, and I know that I can do $5,000.00 of work in a week - I want the spreadsheet to break the work into consecutive week segments so I can schedule the proper amount of work in each week and it will do it automatically even if I shuffle the jobs around.

Any idea how I can do this?

[duane]

I just noticed that

If weeksum > weekbreak Then GoTo newweek

should be

If weeksum >= weekbreak Then GoTo newweek

"duane" wrote:

> how about this macro
>
> Sub weekwork()
> Sheets("sheet2").Select
> ' i set this to insert a row when total is 7 or greater
> weekbreak = 7
> ' data starts in row 3
> i = 2
> start:
> weeksum = 0
> sameweek:
> i = i + 1
> ' data in column 4
> If Cells(i, 4).Value = 0 Then GoTo theend
> weeksum = weeksum + Cells(i, 4).Value
> If weeksum > weekbreak Then GoTo newweek
> GoTo sameweek
> newweek:
> Cells(i + 1, 4).Select
> Selection.EntireRow.Insert
> i = i + 1
> GoTo start
> theend:
> End Sub
>
> "ChrisV" wrote:
>
> >
> > This is my first time using this forum...
> >
> > I am trying to make a schedule that uses the dollar amounts of various
> > jobs to schedule a certain amount of work in a week. For example if I
> > have a hundred jobs that are worth various amounts of money each, and I
> > know that I can do $5,000.00 of work in a week - I want the spreadsheet
> > to break the work into consecutive week segments so I can schedule the
> > proper amount of work in each week and it will do it automatically even
> > if I shuffle the jobs around.
> >
> > Any idea how I can do this?
> >
> >
> > --
> > ChrisV
> > ------------------------------------------------------------------------
> > ChrisV's Profile: http://www.excelforum.com/member.php...o&userid=36346
> > View this thread: http://www.excelforum.com/showthread...hreadid=561298
> >
> >

[duane]

how about this macro

Sub weekwork()
Sheets("sheet2").Select
' i set this to insert a row when total is 7 or greater
weekbreak = 7
' data starts in row 3
i = 2
start:
weeksum = 0
sameweek:
i = i + 1
' data in column 4
If Cells(i, 4).Value = 0 Then GoTo theend
weeksum = weeksum + Cells(i, 4).Value
If weeksum > weekbreak Then GoTo newweek
GoTo sameweek
newweek:
Cells(i + 1, 4).Select
Selection.EntireRow.Insert
i = i + 1
GoTo start
theend:
End Sub

"ChrisV" wrote:

>
> This is my first time using this forum...
>
> I am trying to make a schedule that uses the dollar amounts of various
> jobs to schedule a certain amount of work in a week. For example if I
> have a hundred jobs that are worth various amounts of money each, and I
> know that I can do $5,000.00 of work in a week - I want the spreadsheet
> to break the work into consecutive week segments so I can schedule the
> proper amount of work in each week and it will do it automatically even
> if I shuffle the jobs around.
>
> Any idea how I can do this?
>
>
> --
> ChrisV
> ------------------------------------------------------------------------
> ChrisV's Profile: http://www.excelforum.com/member.php...o&userid=36346
> View this thread: http://www.excelforum.com/showthread...hreadid=561298
>
>