VBA Help - How to use VBA to find a value in another worksheet, and update a cell in that row

[ailleantsian]

Hi, 

I have a master worksheet, in a workbook which contains my catalog items(all unquie values) and prices. Once a year these prices may change and I receive an update of the prices, which includes the catalog item value). 

I have created an import of the sheet which includes only the items that have been updated in price. and once the master is updated that import sheet is then deleted.

What I am trying to do is work down this temp worksheet, and search the master catalog for the catalog number , then move 2 cells across on that row to replace the value with the update. I have tried a couple of ways and none seem to be working as expected. Is there any one that could help ?

[#2]

@ailleantsian  although you could create a macro to do this, what about just creating a new table that merges the values as needed (look for update and if none found then use orig value) and then copy and 'PASTE VALUES' back onto the original table.  you can create this 'merge' table in a separate tab that you can keep for future use also.  Attached is a sample of what I mean except I put all 3 tables on the same sheet for simplicity.

[#3]

@mtarler Thankyou, unfortunately, the master sheet is a part of a wider workbook that works like a cost estimation tool. It needs to be done within the macro so it is ease of use and no risk of the users being able to impact the costs ( the catalog is hidden from users of the workbook)

I have created the VBA when the file is received, that it is able to be selected, create a temp worksheet to add the records, but because we don't receive a full catalog need to do like a Vlook up and replace value but only temp as the temp worksheet will be deleted at the end of the update.

[#4]

OK so I'm really not sure what you want/need. You say you have a macro but it isn't working as expected but haven't included the code nor what happens unexpected. Because excel and VBA have a lot of ways/approaches to solve a problem, me giving you a random example may have no value. The VBA should be rather simple and follow very similar logic as the worksheet I sent. Maybe copy and paste the code here and we can review it and find the issue.

As for easy to use but no risk of users being able to impact the costs you haven't give enough detail on how things are set up, what and how things are protected, and who has what rights and needs. If you have a macro that can automatically change the pricing that seems like high risk unless there are additional security restrictions you aren't mentioning.