Use Formula or Macro to select next available value from a list, then copying as database

[mbelanger]

Hi,
I haven't been able to formulate my question properly to search on the forum as to what I want to achieve, but basically I need a formula or macro to show the next available Code, then copy and paste data next to that code so that it becomes unavailable next search and builds the database, as shown in the attachment.

I want the users to enter the item in cell B3, and their initial in D3. Then with a macro or formula, Excel would return the next available Alphanumeric Code from the list in Column "G". Then it would copy the Item and User under column H/I next to the corresponding Code so that the code becomes unavailable for the next item entry.

It seems simple in principle but can't figure it out. Using Excel 2010. Thanks

[kadeo]

How's this:

Please Login or Register  to view this content.

You have to put in the Code Module for the worksheet and not a standard module. It only does something if either B3 or D3 are updated and if both of them have data so there's no issue with which way round the user enters data. Then it simply finds the next database line that has no item and copies the data.

[Kevin#]

see attached workbook

This is the macro that has been placed in the sheet module
Runs automatically when you amend the value in D3
Checks and exits sub if C3 is not blank or if B3 contains a value
Values in B,C & D deleted after G,H,I updated

Please Login or Register  to view this content.

[mbelanger]

Thanks Kevin#. I've tested your file and does exactly what I needed. cheers

[Kevin#]

You are welcome.

If you add 1 line to @Kadeo 's macro you will find that it does exactly the same thing as my effort. Despite looking looks quite different, the thought process is identical.

add:

Please Login or Register  to view this content.

after:

Please Login or Register  to view this content.