Interactive calculation code

    Hi..
    I'm running a sheet that has a row dependant on another..
    Let's put it like this:
    Row K, has a value which is due date, and row H has a value which is machine to be used. Machine to be used has a range of 1 to 9, and due date, must be less than the delivery date. How can I fill in the machine in row H and read the result in row K, and if row K has a greater value when compared to delivery date row D, the macro will change the machine until it finds a due date lower than the delivery date. Briefly, the macro has to choose machine number 1 in cell H2 , calculate, read the result in cell K2, and if greater than D2, change value in H2. I need this to loop through the whole sheet until row 2000. Can it be done?
    Thank you.

    Re: Interactive calculation code


    I see a few possible solutions, that will depend on the actual data. One uses formulae and teh other VBA.


    Can you post (as Dave suggested) a small file showing a sample of your initial data and perhaps the results assuming only 5 machines?


    Also, to ensure that all machines get similar usage it is common to not allocate the first machine that is available (meaning that the lower the machine number the more likely it is to be used) but the next machine that is available (meaning that all machines will have similar usage). Is this of concern?


    A.

    Re: Interactive calculation code


    Sorry, but the attachment had to be zipped..


    The column F shows the number of days to be consumed by the machine to make the item so that Excel can return the due date in column K. But if the value in column K is greater than in column F it's a sign that the item cannot wait for that machine and it has to pick another. Column J had formulas that will not impact with the VBA or new formula involved, so I removed it. Note that cells that posterior due dates when compared to delivery dates have bold and red color formatted characters.
    Thank you!

    Re: Interactive calculation code


    I think we need to take a step back.


    I have worked out that the headings are in Portugese and what most of them mean.


    The reason the file was so large is that you have formatting on so many cells on the Banco sheet.


    We need to sort the orders on the due date and process them in that order. We then need to allocate each order to a machine. You calculate the due date but I can not follow how you have done it.


    I presume that the Data entrega (Delivery date) is the date you receive something.


    You are presumably given a due date (although you appear to calculate it).


    You want to schedule all of your items such that they get anfactured by the due date, highlighting any that are overdue.


    Am I right so far?


    A.

    Re: Interactive calculation code


    Maybe i used the wrong words, but the Data Entrega is the date the item has to be ready (customer's request). The "due date" I mentioned is supposed to be the date when the item is going to be ready considering that it is going to be using the machine no. 1. As some of the items have the same machine of preference, if I assign the no. 1 machine for all of them, some will only be entering the machine after the previous one has come out. Then I'm gonna have to calculate a new finishing date, accepting the fact that there's an item already being manufactured in that machine, and it has no other machine to be assigned to, and if that finishing date is later than the delivery date specified by the customer then the formatting is applied.
    I'm sorry about the "due date" mistake. It's actually a "job finish date". Hope it clears everything out. Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login