Production planning - retrieve, update data and multiple queries

[swanseaexcel]

Hi guys,

I'm quite new to VBA programming and I have a project I need to complete for work. I'm just mapping out exactly what I want and there are a number of programming instructions I'll need to add to make this work.

This is going to be quite a challenge for me but I thought I'd dive in now before I go back to work on the 4th. If someone's up for a bit of a challenge to start 2011 then all help is appreciated!

In the attached spreadsheet, the plan tab describes what I'm hoping to achieve and am looking for help with. Basically though I need help with the following:

1) VBA code to add row of data to the end of a data range in another tab

2) Code to retrieve and update data in another tab

3) Excel / VBA instructions to retrieve data based on multiple criteria.

Thanks in advance!

[zbor]

Please, rename your title according to the rules:

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[swanseaexcel]

Apologies Zbor, rookie error.

Not sure how best to word the title, there's a lot I'm asking for help with so it's still maybe a little vague.

Hopefully someone will help though.

[watersev]

see attachment

Order number can not be empty or duplicate existing order number in Open orders.
Select or type order number to show order info. Edit and press Update existing order.

The other questions require more details

[swanseaexcel]

Thank you very much Watersev, that's perfect for adding and amending orders to the open orders tab.

Just a couple of queries, I tried adding an order (60002). Entered delivery date as 31/01/2011 but it adds to the list as 09/01/2011?

Also, process 1 was Gawam, process 2 was Duplo and process 3 was Dolce. I'd expect the open orders tab to show gawam in column F (it does), Duplo in column K (it shows Gawam) and Dolce in column P (It's blank).

Also, the next step is to develop the individual machine tabs (e.g. Gawam example tab shows how I hope these tabs will eventually appear).

When an order is added or amended using the code you've added, as well as updating the open orders tab, I want the machine tabs to update if the order has the machine as a process. e.g. in the case of order 60002, the order should appear on the Gawam, Duplo and Dolce tabs as it needs to run on those machines.

The "Gawam example" tab shows how I hope this can be populated. some of the cells will need to be driven by VBA code or some clever formulas if they are to work.

Let me know what you think Watersev or anyone else willing to help.

Thanks again.

[watersev]

please check attachment, try to add order and amend it. If everything is OK we will move to the 3 question

[swanseaexcel]

Thanks Watersev - changes have worked and amend function works perfectly.

Only thing I've noticed is a mistake on my part, whilst there are input fields for quantity and customer in the order input tab, there is no column for these fields in the open orders tab.

We'll need to add those in, perhaps between specification and date stock available.

That asides i'm ready for question 3!!

[watersev]

Questions on fields, sheet Galam
sequence: goes up 10 points every row, starts from 10
available: input by user or needs to be calculated? how?
setup: constanta
production time: machine speed per hour - constanta
processes left: will be calculated on the moment of adding order, it will not track production process
Please reply on each position

[swanseaexcel]

sequence: goes up 10 points every row, starts from 10
10 is the equivalent of 1. Was thinking that if you wanted to insert an order between 10 and 20 (1 and 2) you could add 15 without having to re-number everything after 10.

available: input by user or needs to be calculated? how?
Needs to be calculated - this field will tell the user what the earliest time they can plan production of this order is. Will be the latter of :

1) When stock is available (input by user as "Date stock expected" in order input tab - this is the date the raw materials required to make the order will be in stock by) or,

2) When production has been completed in previous process (if applicable). For example, if you look at order 56546 in open orders tab - this has three processes, firstly on the Gawam machine, then the fuji, then duplo. I'm hoping to have a tab identical in format to the Gawam tab for each machine. The user will have to plan production of the order on each tab, in this case Gawam, Fuju and Duplo tabs.
In the Gawam tab, available will be the date stock expected field, in the Fuji tab, available will be the finish time calculated in the Gawam tab, in Duplo tab it will be finish time in Fuji tab. Does that make sense?

setup: constanta
This will be input by user on the individual machine tab. User will know expected set up time on the machine based on what specification of previous and next product are. Setup time will be added to available time to determine start time.

production time: machine speed per hour - constanta
Will be constant, but varies by machine (maybe include an box where speed to be used can be input by user?)

processes left: will be calculated on the moment of adding order, it will not track production process
This makes sense - may need to consider that user may change machines to run on in amend.

Let me know if you have any further questions Watersev.

[watersev]

check attachment. At the moment it is done only for process 1 and sheet "Gawam" only. Please check if it is ok.

[swanseaexcel]

In terms of the attachment above, I trialled adding some orders and found the following issues:

1) Start time and finish time - had to click enter in these fields to get them to update

2) Production time - nothing appeared in here (should be quantity divided by line speed per hour). Format should be HH:MM. i.e. if quantity is 5,000 and line speed per hour is 2,500, production time should be 02:00. So if start time is 05/01 00:00, user has input setup time of 02:00 and production time is 02:00, then 00:00 + 02:00 + 02:00 = Finish time of 05/01 04:00.

3) On time - format is a number yet result is 31/01/2011. This field should be a number, but should be number of days. I.e. if finish time is 29/01/2011 and due date is 31/01/2011 then on time should be 2 highlighted green to indicate on or ahead of time.

However, if finish time was 02/02/2011 then on time should be -2 and highlighted red to indicate late delivery.

Also, the order I added had 3 processes - Gawam, Duplo and Gawam. This order needs to appear in the Gawam tab twice as it needs to run on that machine twice.

I think we need to add a column after order number to indicate process number. In the case of the order above, in the Gawam tab you would have two identical rows but one would have process number 1 and the other would be 3.

[swanseaexcel]

Thinking how the process will work and in addition to adding a process number column, we need to change "Available" to be "Stock available" and this can constantly reference the "Date stock expected" value input by user. We also need to add a "Previous process finish time" column. This will reference the finish time calculated for the previous process........

Let me illustrate with how I think the process could work. For example the user adds an order with process 1 as Gawam, process 2 as Duplo and process 3 as Gawam again. Here's the steps the user will need to follow:

1) Upon adding the order, two lines appear in Gawam tab (process 1 and 3) and one line in Duplo (process 2) tab for the order. They appear below orders already scheduled with no sequence number, new "Stock available" field will reference the date entered when order was added, new "Previous process finish time" column will be "N/A" for process 1 as it's the first process, and for processes 2 and 3 will be blank and highlighted yellow as this is yet to be determined. Start time, finish time and on time are also blank and highlighted yellow as these are yet to be determined.

2) User will next need to schedule production of the first process. For example, there may already be 5 orders scheduled on the Gawam tab (sequence 10-50) so the next available slot is 60. User enters 60 as the sequence for process 1 and clicks an update button. Macro attached to button re-orders and re-calculates data in table.

Start time will be the finish time of the order at sequence number 50 (the previous scheduled order) plus any setup time the user adds. (** Note this field should flag red if start time is before the new "Stock available" time - maybe even the whole row should flag red**). Production time, finish time and on time for process 1 can now calculate based on existing data.

In Duplo tab, previous process finish time for process 2 of this order is now populated with the finish time of process 1.

2) User can now schedule Duplo machine. User can see finish time of latest scheduled order on Duplo line and also has information for the new order in terms of Stock available and previous process finish time.

This information will help the user determine where to schedule this order. They can schedule it at the end of the list (next available sequence number - multiple of 10) or they can place it between sequenced orders. For example allocating a sequence number of 35 would place the order between 30 and 40.

Once user has allocated a sequence number and pressed update button, tab will update and production, finish and on time will populate for process 2.

Same process is then followed to schedule process 3.


Phew !! Not sure if all of that makes sense but let me know if you need clarification on anything.

Thanks!!