Create a macro for mapping

buttnana, do you specifically want a macro solution? And are you sure you want to overwrite you existing data? This can easily be done, but you will, somewhere, have to create a mapping table. One way would be to use VLOOKUP. Let's say you have the list you indicated in column A above on sheet1 in range A1:A7. Now, on sheet2, in range A1:B7 you have your mapping, i.e. A1="Apple" and B1="Fruit", A2="Volvo" and B2="Car", and so forth for your entire list. If you wanted to use VLOOKUP, and don't need to overwrite your column A, you could put the following formula on sheet1 B1, then drag down...

Please Login or Register  to view this content.

Now, if you want a macro solution, you are still going to need the mapping table, either as a range on a sheet as I described above, or as a hard-coded case statement in your macro code. I would recommend using a range on a sheet, so you don't have to mess with your code every time you need to add items. Which ever method you choose, the macro would be easy if that's what you want/need. If so, let us know, and the code will be posted.

Greg

Ok, so this should do it for you. I will caveat the first sub below with the statement that you need to take care to ensure you type in your mapping values correctly. I took the liberty of also writting a sub to do the mapping the way I would do it, that is, by looking to a sheet range to get the mapping values, but you can decide which you think is better.

First the sub where you hard-code the values in the sub...

Please Login or Register  to view this content.

And now, my choice, the one where you type your mapping values in a range as I described in my first post. This code assumes your list of initial items will be on sheet "List", and the lookup values will be in a range on sheet "Map".

Please Login or Register  to view this content.

Let us know if you have any questions.

Greg

Well, I'm afraid I can't recommend any books, because I am 100% self taught through googling, trial and error, and most importantly, following a good forum, and it looks like you found one. As a reference website, you won't find a more comprehensive one than Chip Pearson's, http://www.cpearson.com/Excel/MainPage.aspx I have never needed to do something with VBA for which he has not had a fully explained and commented example; you should definitely bookmark it. I would also say that if you want to get good, try to solve problems you see here, as they are often scenarios you would never encounter in your normal day. Anyhow, I'm glad my solutions worked for you, and I wish you the best in your quest for VBA improvement.

Greg