Code to Assign Pre-defined Number in separate Worksheet

**cedarhill** · 04-27-2009, 08:41 AM

Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.

I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.

I've attached a sample workbook.

If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.

**JBeaucaire** · 04-27-2009, 09:37 AM

Right-click on the ACTIVITY sheet tab and select VIEW CODE.

Paste in this code:

Please Login or Register  to view this content.

Press Alt-Q to close the editor and save your sheet.
You will have to have macros enabled from now on for this to work.

Start putting Xs in and the assignments will occur.

**cedarhill** · 04-27-2009, 09:58 AM

Thank you very much. It works perfectly.

**mikerickson** · 04-27-2009, 10:13 AM

Putting this in the "Activity" sheet's code module should work.

Please Login or Register  to view this content.

**cedarhill** · 04-27-2009, 12:10 PM

The only problem with this code is that it automatically assigns the two separate PO # types as soon as I enter an "X" in the Assign Temp Unit # field. In many cases, we might assign a Unit # or a repair PO# or a purchase PO#...but rarely on the same activity line.

Unfortunately, I'm not proficient enough with VBA to modify the code so that only when an "X" has been entered in the appropriate "assign" column associated with each type will it assign the type of number requested.

**mikerickson** · 04-27-2009, 06:23 PM

Yes, my bad, I misread the posted sheet. Try this:

Please Login or Register  to view this content.

**JBeaucaire** · 04-27-2009, 08:36 PM

That's what my code in post #2 does. Try that.

**cedarhill** · 04-29-2009, 09:12 AM

Yes, as I stated in my reply to Post #2, the logic worked perfectly. Also, I tested the 2nd code sample provided by mikerickson and that also will work. One of the great benefits of joining the forum is the education one gets in learning different paths to get to the same solution.

One thing I do know for certain is that I have so much more I need to learn! Regardless...thanks to you both.