VBA code to find/replace #ref in formula

[WWII_Buff]

I have searched high and low. Is there a way to find several #REF ! in a formula string within a range (B11:AP55) in a single worksheet and replace it with a "Master".

=SUMIFS(INDEX([URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] !,0,MATCH('SITE'!AP$3,[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] !,0)),[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] !,'SITE'!$A11,[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] !,'SITE'!$A$45)

 

[njimack]

I ran it on a small sample of data and it worked as expected. My code shouldn't have generated that error as it doesn't use any data types. Which line of code is highlighted when you go into the debugger?

 

[WWII_Buff]

I will take another look if you confirm it should have worked in the example I provided.

.Formula = Replace(.Formula, "#REF", "Master", , , vbTextCompare)

 

[WWII_Buff]

@njimack and @jim may

Thank you both! Both methods worked!

 

[kparadise]

I am calling back on this thread.

I have sheet within a workbook that provides details [detail] once a selection from a drop down is created on another sheet [selection]. I also have this massive MACRO I run when I need to update the entire workbook. This MACRO deletes and re-creates a tab called [consolidated]. The [detail] tab has many formulas based from the [consolidated] tab...however, when that tab is deleted, and re-created with new data; the formulas go to "#REF!" instead of "consolidated!". I tried so many things and cant get anything to work. How do I search in the entire [detail] tab and replace ALL of the #REFs with consolidated in ALL formulas?

 

[kparadise]

Actually,

Is there a way to lock in the formulas on the [detail] tab instead of find/replace once the deletion and re-creations of the [consolidation] tab happens? Because not only does the #REF happen; it also removes my ranges. e.g. "consolindated!A:A" turns to "#REF !". So I need the "consolidated" tab name, but also keep my reference cells.