what column is the X in ?

[Paula F]

I am trying to improve production plans in Excel (company is currently forcing us to use microsoft project which is just far too complicated and not the right tool in my opinion) Basically I would have product codes in one column, and then qty to make, sales order date, and hours it take to make it / or number of shifts (whihc I can do a formula to get form the standard hours). I then have days split into shifts across the top of the page (so one column for each shift). I then put a X in the box of the shift when the product is planned to be made. I want to be able to show what the start and finish dates will be for each order. (this is easy in Project), then I can compare finish dates with sales order date to say whihc ones will be late.
Would it be possible to say "look for an X and tell me which date is at the top of the column" ? but there would be more then one X sometimes (if production is for more then one shift), and I would want the start and finish date. Can anyone help ? I would be very grateful !!!!
DOes anyone show production plans in Excel any other way ?
thank you in advance
Paula

 

[Paula F]

HI Jon

thank you very much for this, but unfortunately I am not as good at all this as I would like to be...what do I need to do with all of the formula that you have written ? where do I put it ?

thank you


Paula

 

[just_jon]

That's VBA code.

You need to hit ATL+F11 - that will bring up the VB Editor.

On the left side of that screen you should see a list of Projects. Find your workbook name there.

Beneath your workbook name, find the sheet name holding the X's. Click on it so it is selected.

Copy the code I posted and paste it into the white space in the center/right of the VB Editor screen.

You can now close out the VB Editor.

From now on, on that worksheet, a double-click will move Xs 1 column right, a right-click moves them 1 column to the left. [ I should have built in a check for your 1st column column containing an X and prevented left movement - as it is, it overwrites and loses 1 column. ] See below for modified right-click code.

Also from now on, you will be prompted on opening this workbook with a macro alert. You must answer in the affirmative if you want the code to work.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeRightClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Cells(Target.Row, "D") = "X" <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Cannot move left--already starting in 1st period."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    i = .Match("X", Range(Cells(Target.Row, "E"), Cells(Target.Row, "IU")), 0) + 4 <SPAN style="color:#007F00">' position of first X</SPAN>
    j = .Match("X", Range(Cells(Target.Row, "E"), Cells(Target.Row, "IU"))) + 4 <SPAN style="color:#007F00">' position of last X</SPAN>
    Range(Cells(Target.Row, i), Cells(Target.Row, j)).Copy Cells(Target.Row, i).Offset(0, -1)
    Cells(Target.Row, j).ClearContents
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>