Hello all
Im hoping someone can guide me in the right direction with the best approach for the following:
I would like to make a dynamic sub, which will run through a sheet called RenamingSheet and then replace certain cells in other sheets based on the values/criterias mentioned. The RenamingSheet is set up like below:
RenamingSheet:
RenamingSheet.png
The other 2 sheets I made in the demo file look like this:
Sheet1:
Sheet1.png
Sheet2:
Sheet2.png
The RenamingSheet contains all info to find a specific cell in the other sheets based on 2 criterias. So for instance, with the second row, that looks like this:
Renamingtop.png
the script should find the row in Sheet1 (A) that matches the 2 criterias where SheetColumn1 (C) is Green (D) and SheetColumn2 (E) is Apple (F), and then change SheetColumn3 (B) to 10 (H). There will always only be 1 match and in this case it would be C2 in Sheet1. So the value in [Renaming.xlsx]Sheet1!$C$2 should be changed from 1 to 10.
I made a formula to find that cell (G), but not sure what approach is best for this sub. Wondering if its should be a combo of Evaluate and Find in VBA or if there is some other clever way to make this. I need a dynamic solution, as the values will change repeatedly and I will need to run the replace sub often.
Appreciate any suggestions to the best approach for this!
Edit: Just for clearification, the headers and sheetnames are just made up for this demo - its not called stuff like Sheet1Column1 in my actual setup. Its just to make this demo more transparent. Would just mention that, in case someone was thinking of a solution depending on those specific names.
Bookmarks