Search and Replace - Need Help!

Hi - I need help with my excel sheet; below are the details:

Two tabs ("Sheet1", "Sheet2")

I need to run code to search Column A in "Sheet1" and eventually replace columns B, D and E of the same spreadsheet with information from "Sheet2" depending on the search results.

The code would tell the spreadsheet:
If the first 4 characters of any cell within column A on "Sheet1" match the first 4 characters in any cell in Columns A, B, or C in "Sheet2", Then Columns B, D and E on "Sheet1" would be replaced by Columns D, E and F on "Sheet2" in that specific row.

I hope this makes sense. Thanks for any help in advance!

Hi,

Try these code,

Please Login or Register  to view this content.

If you want to get a modification post a sample workbook. Thank you

Thanks for your reply! I inserted the code and ran it, attached is how the report looks now. It is a step in the right direction but it is not pulling everything I need.

I think the criteria can be simplified a little bit:

I need to search the first 4 characters in Column A of "Sheet1" for a match of for the first 4 characters in Column A in "Sheet2". Where there is a match, Columns B, C, D, E and F in "Sheet1" should match columns B, C, D, E and F from the matching row in "Sheet2".

On the attached you will get an idea of what I'm trying to do. It looks like the code is partially working for some of the data, but not for all.



HD Test sheet with code.xlsm

can you try the above codes, if this code doesn't give you what you want I'll delete it lol.

It worked! Thanks so much! Am I able to include ac clickable button on "Sheet1" that will automatically run the macro when clicked (instead of pressing Alt F11, F5)?

Yeah you can just DEVELOPER >>>> INSERT >>>> CLICK THE COMMAND BUTTON >>>> then a wizard asking if you want to assign macro >>>> then click the name of the macro >>>> then okay >>>> then press the star on the bottom of my post lol.

I click the command button and I paste it onto my workbork, but there is no wizard asking if I want to assign a macro. Any additional information on this step?

Add a button (Form control)

If the Developer tab is not available, display it.

ShowDisplay the Developer tab

On the Developer tab, in the Controls group, click Insert and then, under Form Controls, click Button Button image.

Controls group on the Developer tab

Click the worksheet location where you want the upper-left corner of the button to appear.
The Assign Macro dialog box appears.
Assign a macro to the button, and then click OK.
To specify the control properties of the button, right-click the button, and then click Format Control.

credits to ms site.. if you want to check here is the site http://office.microsoft.com/en-us/ex...010342137.aspx

Don't forget the last step click the "Star button" lol.