Find and Replace a file name used in formulas

[txfrazier]

Hi.


I'm trying to figure out how to find & replace "File_Name_Placeholder" in all formulas contained in the workbook with the actual file name entered by the user in Sheet1: $B$3


For example:

The user enters the file name: abcsheet.xlsx in cell $B$3 on Sheet1


In cell $B$3 on Sheet2 is the formula: ='[File_Name_Placeholder]7 Day'!$D$20


I want a macro that will find all instances of "File_Name_Placeholder" in the workbook and replace it with "abcsheet.xlsx"


I recorded a macro (below) that does the find & replace, but I need it to change the "replace with" based on what the user has input in $B$3 on Sheet 1.


Cells.Find(What:="File_Name_Placeholder", After:=ActiveCell, LookIn:= _

xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Replace What:="File_Name_Placeholder", Replacement:= _

"abcsheet.xlsx", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False _

, SearchFormat:=False, ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate


Thanks for your help!


Thomas

 

[Hui]

[pre]

Cells.Find(What:="File_Name_Placeholder", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Replace What:="File_Name_Placeholder", Replacement:= _
Worksheets("Sheet1").Range("$B$3").Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False _
, SearchFormat:=False, ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

[/pre]

 

[txfrazier]

Hui,

Thanks! The script works but only for one cell at a time (i.e., I have to keep running the macro.) What do I need to change to have it change all of the references on 4 different sheets?


Thomas

 

[txfrazier]

Got it!!


Cells.Replace What:="File_Name_Placeholder", Replacement:= _

Worksheets("Sheet1").Range("$B$3").Value, LookAt:=xlPart, SearchOrder:=xlByRows, _

MatchCase:=False _

, SearchFormat:=False, ReplaceFormat:=False


This does the seach and replace in all sheets.


Thanks Hui for pointing me in the right direction! You guys rock!!!