Forum Discussion
VBA Help - How to use VBA to find a value in another worksheet, and update a cell in that row
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.
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.
- 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.
