I have a spreadsheet that contains material requirements for specific jobs.
Each row shows the job number, item number, quantity required for this job,
the total quantity required for all jobs and the quantity on hand for the
specific item in this row. The combination of job number and item number is
unique for each job. I need some help in generating a listing of what jobs
can be worked based on the on hand quantity in Column E.

Here is an example of the worksheet I am using.

Column A Column B Column C Column D Column E
Job Number Description Qty Req'd TTLQty Req'd On Hand Qty
Job 1 Item #1 12 24
35
Job 1 Item #3 11 11
10
Job 1 Item #6 14 26
26
Job 1 Item #4 10 10
15
Job 1 Item #9 33 49
50
Job 2 Item #6 12 26
26
Job 2 Item #9 16 49
50
Job 2 Item #7 18 18
50
Job 2 Item #2 20 20
20
Job 2 Item #1 12 24
35


I am at a loss with how to do this. Is there anyone that can help me figure
this out?

Regards,

WShelton

I've done something similar. I've made changes to my code to reflect
what you are trying to do, but you will probably have to tweak it to fit
your needs. Also, sheet names will be different.

Sub Make_sched()

Dim intCounter As Integer, intMoveTo As Integer
Dim intNumRows As Integer, intPart As Integer
Dim strMoveCond As String
intCounter = 2
intMoveTo = 2

Sheets("Today").Range("A1:I500").Clear
Sheets("Master").Range("A1:A7").Copy _
Destination:=Sheets("Today").Range("A1")
intNumRows = Worksheets("Master").Cells(500, 1).End(xlUp).Row + 1

While intCounter < intNumRows

If Sheets("Master").Cells(intCounter, "D").Value
Sheets("Master").Cells(intCounter, "C").Value Then
strMoveCond = "NO"
Else
strMoveCond = "YES"
End If 'Allows blank lines to be copied

If strMoveCond = "YES" Then
Sheets("Master").Select
Range(Cells(intCounter, "A"), Cells(intCounter, "E")).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Today").Select
Range(Cells(intMoveTo, "A"), Cells(intMoveTo, "E")).Select
ActiveSheet.Paste

intMoveTo = intMoveTo + 1
End If

intCounter = intCounter + 1

Wend

Columns("A:E").AutoFit

Application.Goto Reference:=Worksheets("Today").Cells(1, 1)

End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Could you not just put a formula in F

say in F2: =IF(E2C2,"Ready","")

and copy down, and the autofilter column F on Ready

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bluegrass Fanatic" wrote in message
...
I have a spreadsheet that contains material requirements for specific
jobs.
Each row shows the job number, item number, quantity required for this
job,
the total quantity required for all jobs and the quantity on hand for the
specific item in this row. The combination of job number and item number
is
unique for each job. I need some help in generating a listing of what
jobs
can be worked based on the on hand quantity in Column E.

Here is an example of the worksheet I am using.

Column A Column B Column C Column D Column E
Job Number Description Qty Req'd TTLQty Req'd On Hand Qty
Job 1 Item #1 12 24
35
Job 1 Item #3 11 11
10
Job 1 Item #6 14 26
26
Job 1 Item #4 10 10
15
Job 1 Item #9 33 49
50
Job 2 Item #6 12 26
26
Job 2 Item #9 16 49
50
Job 2 Item #7 18 18
50
Job 2 Item #2 20 20
20
Job 2 Item #1 12 24
35


I am at a loss with how to do this. Is there anyone that can help me
figure
this out?

Regards,

WShelton

I'm not sure of a couple of items:
1. In the screen shot it shows Column E On Hand Qty (with no numbers below).
Then there is an extra row below each Job Num (35, 10, 26, etc). Are these
numbers meant to be in Col E? If so, then does col E represent the Qty on
Hand for any Job or just the "Specific item in this row" as stated in your
post?
In otherwords for Job 1, Item 1 you need 12. If you have 35 On Hand does
that leave 23 available for Job 2, Item 1?

2. What if you have sufficient Qty On Hand for all Items in Job 1 EXCEPT for
one (or more) Items? Do you hold the Job 1 Items that are currently on hand
specifically for Job 1 until the remaining Items become available, OR do you
free up all the Items and make them available for use by the next Job?

3. Is there any priority for assigning Items other than the Job number?

Just assuming that
1. the priority is the same as Job #
2. Col E represents your total inventory available for any job
3. if All items are NOT On Hand for Job 1, then All items will them be free
for use on Job 2

I have put together a worksheet that will accomplish the task, if I can
figure out
how to attach it to this reply or to email it to you.

"Bluegrass Fanatic" wrote:

I have a spreadsheet that contains material requirements for specific jobs.
Each row shows the job number, item number, quantity required for this job,
the total quantity required for all jobs and the quantity on hand for the
specific item in this row. The combination of job number and item number is
unique for each job. I need some help in generating a listing of what jobs
can be worked based on the on hand quantity in Column E.

Here is an example of the worksheet I am using.

Column A Column B Column C Column D Column E
Job Number Description Qty Req'd TTLQty Req'd On Hand Qty
Job 1 Item #1 12 24
35
Job 1 Item #3 11 11
10
Job 1 Item #6 14 26
26
Job 1 Item #4 10 10
15
Job 1 Item #9 33 49
50
Job 2 Item #6 12 26
26
Job 2 Item #9 16 49
50
Job 2 Item #7 18 18
50
Job 2 Item #2 20 20
20
Job 2 Item #1 12 24
35


I am at a loss with how to do this. Is there anyone that can help me figure
this out?

Regards,

WShelton

Thanks gocush. Sorry for getting back so late. You're correct in that the
extra row containing the numbers was meant to represent the data for Column
E. If you have an example you could send to
remove the NOSPAM to use.


"gocush" wrote in message
...
I'm not sure of a couple of items:
1. In the screen shot it shows Column E On Hand Qty (with no numbers
below).
Then there is an extra row below each Job Num (35, 10, 26, etc). Are
these
numbers meant to be in Col E? If so, then does col E represent the Qty on
Hand for any Job or just the "Specific item in this row" as stated in your
post?
In otherwords for Job 1, Item 1 you need 12. If you have 35 On Hand does
that leave 23 available for Job 2, Item 1?

2. What if you have sufficient Qty On Hand for all Items in Job 1 EXCEPT
for
one (or more) Items? Do you hold the Job 1 Items that are currently on
hand
specifically for Job 1 until the remaining Items become available, OR do
you
free up all the Items and make them available for use by the next Job?

3. Is there any priority for assigning Items other than the Job number?

Just assuming that
1. the priority is the same as Job #
2. Col E represents your total inventory available for any job
3. if All items are NOT On Hand for Job 1, then All items will them be
free
for use on Job 2

I have put together a worksheet that will accomplish the task, if I can
figure out
how to attach it to this reply or to email it to you.

"Bluegrass Fanatic" wrote:

I have a spreadsheet that contains material requirements for specific
jobs.
Each row shows the job number, item number, quantity required for this
job,
the total quantity required for all jobs and the quantity on hand for the
specific item in this row. The combination of job number and item number
is
unique for each job. I need some help in generating a listing of what
jobs
can be worked based on the on hand quantity in Column E.

Here is an example of the worksheet I am using.

Column A Column B Column C Column D Column E
Job Number Description Qty Req'd TTLQty Req'd On Hand Qty
Job 1 Item #1 12 24
35
Job 1 Item #3 11 11
10
Job 1 Item #6 14 26
26
Job 1 Item #4 10 10
15
Job 1 Item #9 33 49
50
Job 2 Item #6 12 26
26
Job 2 Item #9 16 49
50
Job 2 Item #7 18 18
50
Job 2 Item #2 20 20
20
Job 2 Item #1 12 24
35


I am at a loss with how to do this. Is there anyone that can help me
figure
this out?

Regards,

WShelton

Thanks Bob but I've tried that one. "Ready" appears by several items that
are associated with a job but I need to know what jobs show all parts as
"Ready" then generate a list of those jobs on another sheet. That's what
turning out to be a tough one for me to figure out.

"Bob Phillips" wrote in message
...
Could you not just put a formula in F

say in F2: =IF(E2C2,"Ready","")

and copy down, and the autofilter column F on Ready

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bluegrass Fanatic" wrote in message
...
I have a spreadsheet that contains material requirements for specific
jobs.
Each row shows the job number, item number, quantity required for this
job,
the total quantity required for all jobs and the quantity on hand for the
specific item in this row. The combination of job number and item number
is
unique for each job. I need some help in generating a listing of what
jobs
can be worked based on the on hand quantity in Column E.

Here is an example of the worksheet I am using.

Column A Column B Column C Column D Column E
Job Number Description Qty Req'd TTLQty Req'd On Hand Qty
Job 1 Item #1 12 24
35
Job 1 Item #3 11 11
10
Job 1 Item #6 14 26
26
Job 1 Item #4 10 10
15
Job 1 Item #9 33 49
50
Job 2 Item #6 12 26
26
Job 2 Item #9 16 49
50
Job 2 Item #7 18 18
50
Job 2 Item #2 20 20
20
Job 2 Item #1 12 24
35


I am at a loss with how to do this. Is there anyone that can help me
figure
this out?

Regards,

WShelton

Thanks Claud but I'm programmatically challenged and I'm not sure I
understand how this works. I copied it over to my sheet and ran it. But I
guess I wasn't tweaking it right. How does it work?

"Claud Balls" wrote in message
...
I've done something similar. I've made changes to my code to reflect
what you are trying to do, but you will probably have to tweak it to fit
your needs. Also, sheet names will be different.

Sub Make_sched()

Dim intCounter As Integer, intMoveTo As Integer
Dim intNumRows As Integer, intPart As Integer
Dim strMoveCond As String
intCounter = 2
intMoveTo = 2

Sheets("Today").Range("A1:I500").Clear
Sheets("Master").Range("A1:A7").Copy _
Destination:=Sheets("Today").Range("A1")
intNumRows = Worksheets("Master").Cells(500, 1).End(xlUp).Row + 1

While intCounter < intNumRows

If Sheets("Master").Cells(intCounter, "D").Value
Sheets("Master").Cells(intCounter, "C").Value Then
strMoveCond = "NO"
Else
strMoveCond = "YES"
End If 'Allows blank lines to be copied

If strMoveCond = "YES" Then
Sheets("Master").Select
Range(Cells(intCounter, "A"), Cells(intCounter, "E")).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Today").Select
Range(Cells(intMoveTo, "A"), Cells(intMoveTo, "E")).Select
ActiveSheet.Paste

intMoveTo = intMoveTo + 1
End If

intCounter = intCounter + 1

Wend

Columns("A:E").AutoFit

Application.Goto Reference:=Worksheets("Today").Cells(1, 1)

End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!