Macro to change formula

Hi all,

I'm trying to produce what I think should be a reasonably simple macro.

I have multiple business units in the business and, based on which business unit is selected the formulas should then consolidate data from other sheets into a final presentation.

For example; two business units are 282 and 302. If a user selects business unit 282 then I want the macro to then go off to several different sheets. In those sheets it should replace all formula references to any other business unit (lets say it's 302) and replace those references with a reference to business unit 282.

In order to make sure the macro runs correctly next time the final part of the macro is to store the newly selected business unit, hard coded into another cell. My plan was that the user selects a new business unit from a drop down list and then runs the macro. When the macro is run the first command is that this cell (with the previously copied business unit hard coded) would be selected, the value copied and, using the find all command, pasted into the "find" field. Then the drop down cell would be selected and whatever business unit the user has picked, this would be copied and pasted into the "Replace" field.

Then three sheets would be selected, one at a time, the columns highlighted and the "Replace All" command would be executed.

Once this has been done the newly picked business unit would be pasted special, value only, into another cell. Then the macro ends. When the macro is run again it's first command is to go to this cell and copy the value to paste into the "find" field. Thus the macro should update and whether the user picks business unit 339 or 612 next time should make no difference.


My problem is demonstrated in the excerpt of the macro that I've pasted below. The business units 282 and 302 are coded into the macro and so the macro will only run for these business units.

How do I get the macro to be dynamic enough to pick up whatever the new business unit has been selected?


Excerpt of macro:

Please Login or Register  to view this content.

End macro excerpt

Please edit your post and add code tags to the code.




AA CodeTags.jpg

Hi Wizards,

We were expecting you to edit your original post with the code tags

Excellent

Does this do the job?

Please Login or Register  to view this content.

OldVal is found in cell A2 and NewVal is found in cell c11. Not sure why cell A2 would be referenced.

I'm confused. A2 is Row 2 Column 1 or Cells(2,1) Is there another sheet involved i.e. Three Sheets??

Sure - but do you mean that no replacement is occurring - could it be the apostrophe?

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.