I have a formula like this:
=IF(OR($A1="xyz",$B1="abc",$C5="dmz"),1,0)
I'd like to replace each cell address with a static address that explicitly states the worksheet, i.e.
=IF(OR(Sheet1!$A$1="xyz",Sheet1!$B$1="abc",Sheet1!$C$5="dmz"),1,0)
I have this:
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, e.g. "$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 accomplish what is basically (pseudocode):
find all instances of "[$][alpha]+"
replace with sheetname & "!" & instance & "$"
Is there a simpler way to perform this substitution?
In the "Excel Options" window that appears, select "Advanced" from the list of options on the left side of the window. Scroll down to the "Display options for this worksheet" section and check the box next to "Show placeholders for empty cells." Finally, click "OK" at the bottom of the window to save your changes.
Explanation. The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text.
Regex to find and replace all matches Instead, the VBA RegExp object provides the Global property that defines whether to search for all occurrences or only the first one. In the code of our function, the Global property is set to True, meaning the pattern should be tested against all possible matches in a string.
Not completely tested but would something like this help? Select a single cell which has a formula and run Sample
. I have not done any error handling. I am assuming that the ActiveCell
WILL have a formula. I am also going with 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)
A slightly more complex 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))
Followup from 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With