I read through my last post and it made no sense at all. Its kinda har
to explain so let me try again.

Here is the part numbers we are running with the number we need t
run.

25752599 = 3
25747753 = 2
25747755 = 1
25752597 = 3
25747761 = 0
25747763 = 3
25752595 = 2
25747769 = 0
25747771 = 2
25752602 = 1
25747741 = 0
25747749 = 1

The part numbers are in cells A4:A15 and the quanity are in cell
B4:B15.

Now A16 = 12 meaning team one has to build 12 of these.
And A17 = 6 meaning team two has to build 6 of these.

What I was wondering is if I could write something that would separat
these giving team one 12 of the 18 (listed in cells C4:C15) and tea
two 6 of the 18 (listed in cells D4:D15).

But the numbers in B4:B15 can change quantities. As can A16 and A17.

I hope this is possible, it will save me a lot of time.

-TyeJae

--
Message posted from http://www.ExcelForum.com

Try this - i assigned names team1 and team2 to your cells
a16 and a17

John

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
Dim team1goal As Integer
Dim team2goal As Integer
Dim team1total As Integer
Dim team2total As Integer
Dim i As Integer
team1goal = Range("team1").Value
team2goal = Range("team2").Value
team1total = 0
team2total = 0
For i = 4 To 15
If team1total = team1goal Then GoTo team2
If Cells(i, 2).Value + team1total < team1goal _
Then GoTo onlyteam1
team2total = Cells(i, 2).Value - _
(team1goal - team1total)
Cells(i, 3).Value = team1goal - team1total
Cells(i, 4).Value = Cells(i, 2).Value - _
Cells(i, 3).Value
team1total = team1goal
GoTo nexti
onlyteam1:
team1total = Cells(i, 2).Value + team1total
Cells(i, 3).Value = Cells(i, 2).Value
GoTo nexti
team2:
If team2total = team2goal Then GoTo done
If Cells(i, 2).Value + team2total < team2goal _
Then GoTo onlyteam2
Cells(i, 4).Value = team2goal - team2total
GoTo done
onlyteam2:
team2total = Cells(i, 2).Value + team2total
Cells(i, 4).Value = Cells(i, 2).Value
nexti:
Next i
done:
End Sub

-----Original Message-----
I read through my last post and it made no sense at all.
Its kinda hard
to explain so let me try again.

Here is the part numbers we are running with the number
we need to
run.

25752599 = 3
25747753 = 2
25747755 = 1
25752597 = 3
25747761 = 0
25747763 = 3
25752595 = 2
25747769 = 0
25747771 = 2
25752602 = 1
25747741 = 0
25747749 = 1

The part numbers are in cells A4:A15 and the quanity are
in cells
B4:B15.

Now A16 = 12 meaning team one has to build 12 of these.
And A17 = 6 meaning team two has to build 6 of these.

What I was wondering is if I could write something that
would separate
these giving team one 12 of the 18 (listed in cells
C4:C15) and team
two 6 of the 18 (listed in cells D4:D15).

But the numbers in B4:B15 can change quantities. As can
A16 and A17.

I hope this is possible, it will save me a lot of time.

-TyeJae-


---
Message posted from http://www.ExcelForum.com/

.

I labeled A16 and A17 as team1 and team2 respectively. Then I copie
the code exatly as you put it and for some reason it isn't doin
anything.

Is there something else I should be doing?

-TyeJae

--
Message posted from http://www.ExcelForum.com

did you assign those range names to the cells?

cells b4-b15 has the # of each part?

a16 and a17 are the two totals?

maybe substitute a16 where team1 is and 17 where team2 is
if you are not sure of the range names

John

-----Original Message-----
I labeled A16 and A17 as team1 and team2 respectively.
Then I copied
the code exatly as you put it and for some reason it
isn't doing
anything.

Is there something else I should be doing?

-TyeJae-


---
Message posted from http://www.ExcelForum.com/

.

Ok I got that to work, one small problem though, it does not evenl
distribute the part numbers. How could I do this? Thanks for you
help!

-TyeJae

--
Message posted from http://www.ExcelForum.com

Please explain more clearly what you want by "more evenly
distribute the part numbers".

John

-----Original Message-----
Ok I got that to work, one small problem though, it does
not evenly
distribute the part numbers. How could I do this?
Thanks for your
help!

-TyeJae-


---
Message posted from http://www.ExcelForum.com/

.

When I run the macro it fills in the cells to look like this:

25752599 3 3
25747753 2 2
25747755 1 1
25752597 3 3
25747761 0 0
25747763 3 3 0
25752595 2 2
25747769 0 0
25747771 2 2
25752602 1 1
25747741 0 0
25747749 1 0

So basically if team 1 would always build the first 5-7 part number
and team 2 would always build the last 5-7 part numbers. Could it b
more even like:

25752599 3 2 1
25747753 2 1 1
25747755 1 1 0
25752597 3 2 1
25747761 0 0 0
25747763 3 2 1
25752595 2 1 1
25747769 0 0 0
25747771 2 1 1
25752602 1 1 1
25747741 0 0 0
25747749 1 1 0

-TyeJae

--
Message posted from http://www.ExcelForum.com

That didn't fill in correctly, the first one should look like this:

25752599...3...3...0
25747753...2...2...0
25747755...1...1...0
25752597...3...3...0
25747761...0...0...0
25747763...3...3...0
25752595...2...0...2
25747769...0...0...0
25747771...2...0...2
25752602...1...0...1
25747741...0...0...0
25747749...1...0...

--
Message posted from http://www.ExcelForum.com

Anybody know if this is possible?

-TyeJae

--
Message posted from http://www.ExcelForum.com

What exactly do you want? I thought you wanted to assign
parts to team 1 until you got up to 12, and then to team 2
until you got up to 6.

The below is the results of my macro.

Can you be more clear?

25752599 3 3
25747753 2 2
25747755 1 1
25752597 3 3
25747761 0 0
25747763 3 3 0
25752595 2 2
25747769 0 0
25747771 2 2
25752602 1 1
25747741 0 0
25747749 1 1
12
6



-----Original Message-----
Anybody know if this is possible?

-TyeJae-


---
Message posted from http://www.ExcelForum.com/

.