Is there an easy way to replace placeholders in Excel?

Question

I have a formula like this:

=IF(OR($A1="xyz",$B1="abc",$C5="dmz"),1,0) 

I would like to replace each cell address with a static address that explicitly indicates a worksheet, i.e.

 =IF(OR(Sheet1!$A$1="xyz",Sheet1!$B$1="abc",Sheet1!$C$5="dmz"),1,0) 

I have it:

 Public Function absoluteFormula(sheetname As String, ByVal formula As String) As String Dim re As New RegExp Dim matches As MatchCollection Dim mtch As Match Dim absoluteAddress As String 'get all addresses in formula re.pattern = "[$][A-Za-z]+[0-9]+" re.Global = True Set matches = re.Execute(formula) 'replace each address with its static version For Each mtch In matches absoluteAddress = sheetname & "!" & getAbsoluteAddress(re, mtch.value) formula = Replace(formula, mtch.value, absoluteAddress) Next absoluteFormula = formula End Function 'makes row static, eg "$AU1" -> "$AU$1" Private Function getAbsoluteAddress(re As RegExp, address As String) Dim matches As MatchCollection Dim alphaColumn As String re.pattern = "[A-Za-z]+" Set matches = re.Execute(address) alphaColumn = matches(0).value getAbsoluteAddress = Replace(address, alphaColumn, alphaColumn & "$") End Function 

This seems like a lot of code to execute what is basically (pseudocode):

 find all instances of "[$][alpha]+" replace with sheetname & "!" & instance & "$" 

Is there an easier way to do this lookup?

Answer 1

Not fully tested , but something like this help? Select one cell with the formula and run Sample . I did not perform error handling. I assume that the ActiveCell formula will have a formula. I'm also going to what you said in the above comment that your formula will not have Named Ranges

 Dim sformula As String Dim sh As String Sub Sample() Dim cell As Range, c As Range '~~> This is what you want to append sh = "Sheet1!" '~~> Store the formula in a variable sformula = ActiveCell.Formula Debug.Print sformula '~~> Get the precedents Set cell = ActiveCell.Precedents '~~> Loop though them For Each c In cell ReplaceAddress c.Address '~~> $A$1 ReplaceAddress c.Address(RowAbsolute:=False) '~~> $A1 ReplaceAddress c.Address(ColumnAbsolute:=False) '~~> A$1 ReplaceAddress c.Address(RowAbsolute:=False, ColumnAbsolute:=False) '~~> A1 Next Debug.Print sformula End Sub Function ReplaceAddress(s As String) As String Dim pos As Long pos = InStr(1, sformula, s) Do While pos > 0 If pos = 1 Then sformula = sh & sformula ElseIf pos > 1 Then '~~> Various checks for "!","$" and ":" If Mid(sformula, pos - 1, 1) <> "!" And Mid(sformula, pos - 1, 1) <> "$" And _ Mid(sformula, pos - 1, 1) <> ":" And Mid(sformula, pos - 2, 1) <> ":" Then sformula = Left(sformula, pos - 1) & sh & Mid(sformula, pos) End If End If '~~> Find next occurance pos = InStr(pos + 1, sformula, s) Loop ReplaceAddress = sformula End Function 

Various tests

Before:

 =IF(OR($A1="xyz",$B1="abc",$C5="dmz"),1,0) 

After:

 =IF(OR(Sheet1!$A1="xyz",Sheet1!$B1="abc",Sheet1!$C5="dmz"),1,0) 

---

Before:

 =VLOOKUP(K4,N10:Q18,1,0) 

After:

 =VLOOKUP(Sheet1!K4,Sheet1!N10:Q18,1,0) 

---

Somewhat more complicated test

Before:

 =IF(G4>MAX($D$4:$D$8),"N/A",INDEX($B$4:$B$8,INDEX(MATCH(G4,$C$4:$C$8,1),0,0),0)) 

After:

 =IF(Sheet1!G4>MAX(Sheet1!$D$4:$D$8),"N/A",INDEX(Sheet1!$B$4:$B$8,INDEX(MATCH(Sheet1!G4,Sheet1!$C$4:$C$8,1),0,0),0)) 

Watching Comments

Use this

 Sub Sample() Dim cell As Range, c As Range '~~> This is what you want to append sh = "Sheet1!" '~~> Store the formula in a variable sformula = ActiveCell.Formula Debug.Print sformula '~~> Get the precedents Set cell = ActiveCell.Precedents '~~> Loop though them For Each c In cell ReplaceAddress c.Address '~~> $A$1 ReplaceAddress c.Address(RowAbsolute:=False) '~~> $A1 ReplaceAddress c.Address(ColumnAbsolute:=False) '~~> A$1 ReplaceAddress c.Address(RowAbsolute:=False, ColumnAbsolute:=False) '~~> A1 sformula = Replace(sformula, c.Address(RowAbsolute:=False), c.Address) sformula = Replace(sformula, c.Address(ColumnAbsolute:=False), c.Address) sformula = Replace(sformula, c.Address(RowAbsolute:=False, ColumnAbsolute:=False), c.Address) Next Do While InStr(1, sformula, "$$") sformula = Replace(sformula, "$$", "$") Loop Debug.Print sformula End Sub Function ReplaceAddress(s As String) As String Dim pos As Long pos = InStr(1, sformula, s) Do While pos > 0 If pos = 1 Then sformula = sh & sformula ElseIf pos > 1 Then '~~> Various checks for "!","$" and ":" On Error Resume Next If Mid(sformula, pos - 1, 1) <> "!" And Mid(sformula, pos - 1, 1) <> "$" And _ Mid(sformula, pos - 1, 1) <> ":" And Mid(sformula, pos - 2, 1) <> ":" Then sformula = Left(sformula, pos - 1) & sh & Mid(sformula, pos) End If On Error GoTo 0 End If '~~> Find next occurance pos = InStr(pos + 1, sformula, s) Loop ReplaceAddress = sformula End Function