VBA to Find and Replace Using a Lookup Table - But only Certain Columns/Multiple Sheets

Sometimes VBA is overkill. A VLOOKUP() formula will accomplish what you want, in just a few seconds.

1) In BM2: =VLOOKUP(C2, Values!$A:$B, 2, 0)
2) Doubleclick the lower right corner of BM2 to copy down the whole data set
3) Copy BM2:BM161
4) Click on C2 and select Paste Special > Values
5) Clear column BM, or repeat for column F.

If you might have blank cells in the column and don't want the N/A error, use this:


Now, if you really want a macro, click on a cell in any column on any sheet and run this macro to do the same thing:

Please Login or Register  to view this content.

If you don't want the tediumn of clicking on a cell before running the macro, it could also be tweaked to go across every cell in row1 and run itself on any column that has "Destination" in the header.

Hi JBeaucaire, thanks very much for replying.

In terms of context I won't be the end user of the workbook, and the people that will won't be skilled enough to use vlookups unfortunately. The data that looking to manipulate is being exported from an 4 access 2003 databases (8 tables in total) using the following code for each table

Please Login or Register  to view this content.

The user will have a command button for each table (there are 8 in total) and exporting to an excel workbook.

Unfortunately the data in the access tables is referenced to a lookup table, so the data I'll get in the excel sheet won't have the true values (locations), and hence why I'll need to replace these in each of the 8 sheets.

The macro I was aiming to get would enable the end user to press one command button to replace the values in certain columns C/F/G etc across all 8 sheets without having to highlight cells or really need to manipulate any of the data if that makes sense.

That's why I thought it would be easier to manipulate the code to only search, replace in certain columns across multiple sheets.

Thanks

David

What about my idea? Search every sheet and for each column with the word "Destination" in the header, run my macro on that column?

Dave, I've asked the question twice with no answer yet. I'm trying to find a way to teach the macro to find the columns where it needs to run ON ITS OWN, without you needing to teach it specifically. It looks like the columns where you want to insert these city names are all called DestinationX in the row1 title row.

Am I correct? Could we do it that way? Run the macro on every column with "Destination" in the header?

Before you offer any other information, please answer that question.

Are the 8 sheets always named the same thing? Or do we need to run it on all sheets in the workbook EXCEPT Values and Values2?

This version will run on every sheet in the workbook that doesn't have "Value" in the sheet name:

Please Login or Register  to view this content.

I'm certainly willing to look at the troublesome workbook. Zip it up and attach it here.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

oops! I left an "Activesheet" reference in the new macro in post #10. I've highlighted and corrected that line of code. That error would cause exactly the kind of error you experienced. Try it with that correction.

I would have to see the macro and the troublesome workbook together.