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)

 

[jim may]

What version of Excel are you running?

 

[WWII_Buff]

Hi Jim! 2016

 

[njimack]

Sub test()
With Range("B11:AP55").SpecialCells(xlCellTypeFormulas)
    .Formula = Replace(.Formula, "#REF", "Master", , , vbTextCompare)
End With

End Sub

 

[WWII_Buff]

Hi Neil! I am getting a Run-Time 13 Error Type Mismatch

 

[WWII_Buff]

Hi Neil! I am getting a Run-Time 13 Error Type Mismatch

 

[jim may]

Beginning with Excel 2013 and Since - there is a NEW function/formula = FORMULATEXT(A1) that might be helpful to you.

 

[WWII_Buff]

Thank you for your help Jim - I was thinking along the lines of Neil - but this vba is erroring out.

To give you more context, I blowing away the master sheet and would like to link back to it when it is recreated.

 

[jim may]

Thank you for your help Jim - I was thinking along the lines of Neil - but this vba is erroring out.

To give you more context, I blowing away the master sheet and would like to link back to it when it is recreated.

I think you can wrap the INDIRECT() around your "Master" sheet name, and doing so will not cause it to be replaced with #REF when it is "blown-away".

 

[WWII_Buff]

I think you can wrap the INDIRECT() around your "Master" sheet name, and doing so will not cause it to be replaced with #REF when it is "blown-away".

Dude! I like where you are going with this! Give me a sec to try it out!