Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a simple way to replace with placeholders in Excel?

Tags:

regex

excel

vba

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?

like image 295
sigil Avatar asked May 28 '15 19:05

sigil


People also ask

How do you create a placeholder in Excel?

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.

How do I replace a character in Excel with another character?

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.

Can you use regex in Find and Replace in Excel?

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.


1 Answers

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
like image 57
Siddharth Rout Avatar answered Oct 14 '22 20:10

Siddharth Rout