Find and replace macro

[edeaston]

Hi,

I am preparing a template document for use within my organisation which will
be used to populate a monthly report.

The template has formula references to multiple Excel files that are
produced in monthly downloads from our systems.

I want to create a macro so that the user can enter the location of the
existing file in one cell (A2), the location of the new file in another cell
(B2) and then run a macro which does a find and replace of all instances of
A2 with B2. As part of the macro I also want it to update A2 with the data
from B2 and clear out the data in B2.
There are multiple links that will be updated so it would good to work out a
way that can update multiple links (e.g. A2 with B2, A3 with B3, A4 with B4
etc) in one go.

Could one of you good people help me out?

Thanks

Ed

 

[Gary''s Student]

Consider:

Sub suby()
Dim s As String, t As String, rr As Range
s = Range("A2").Value
t = Range("B2").Value
Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
For Each r In rr
r.Formula = Replace(r.Formula, s, t)
Next
Range("A2").Value = Range("B2").Value
Range("B2").Clear
End Sub

 

[edeaston]

Hi,

That seems to do the job nicely. The one small problem I have is that once I
run the macro I cant undo any changes made (ctrl+z) - is that standard for
macros or could we add / change some code to allow undo to work again?

Thanks

Ed

 

[Gary''s Student]

Undo will not work.