CODEWITHSUNDEEP
regexvbaexcel
mountainclimber11
mountainclimber11

Reputation: 1400

vba: regex, remove all but cell reference row references from formula strings

In VBA I am trying to build a generalized function that turn strings like these:

a) =IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)

b) =IF(FZ$16=(BDP($C24,FZ$18,FZ$19,"EQY_FUND_CRNCY",FX)),FZ$17,IF($B24="","",BDP($C24,FZ$18,FZ$19,"EQY_FUND_CRNCY",FX)))

c) =IF(ISNUMBER(FU24),TRUNC((((COUNTIF($J$23:$J$2515,$J24)-(SUMPRODUCT(($J$23:$J$2515=$J24)*(FU24<FU$23:FU$2515))))/COUNTIF($J$23:$J$2515,$J24)))*100,2),FX$17)

d) =IFERROR(PERCENTRANK(EO$23:EO$2515,EO24,3)*(-100)+100,ET$17)

e) =BDP($C24,EH$18,EH$19,"EQY_FUND_CRNCY",FX)

Into these:

a) 23 2515 24 17

b) 16 24 18 19 17 24 24 18 19

c) 24 23 2515 24 23 2515 24 24 23 2515 23 2515 24 17

d) 23 2515 24 17

e) 24 18 19

In other words, remove everything except cell reference rows and separate them with spaces (or some other deliminator) so I can VBA.split(x," ") them later.

Notes:

  1. Numbers that aren't part of a cell references are removed.
  2. To use this function you must have the regular expression library. If the code below doesn't work for you include the library: http://support.microsoft.com/kb/818802. (Side note: if you know how to include the library in the code without having to follow those instructions, please share.)
  3. The list of formulas in this example is just an example. I am looking for a generalized solution.

  4. I built this little test sub that might be helpful (IT DOESN'T DO WHAT I WANT):

    Sub test()

Dim s As String
s = "=IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)"
Dim s2 As String
Dim s3 As String
Dim s1 As String
Static re As RegExp
If re Is Nothing Then Set re = New RegExp
re.IgnoreCase = True
re.Global = True
re.Pattern = "[$]"
s1 = re.Replace(s, "")
re.Pattern = "[^A-Z0-9 ]"
s2 = re.Replace(s1, " ")
re.Pattern = "[^0-9]"
s3 = re.Replace(s2, " ")
Debug.Print s3
End Sub

Upvotes: 1

Views: 357

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

Try:

Sub test()

    Dim s As String, matches, m

    s = "=IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)"

    Static re As Object 
        If re Is Nothing Then

            Set re = CreateObject("VBScript.RegExp") 'late binding

            re.IgnoreCase = True
            re.Global = True
            re.Pattern = "[A-Z]+\$?(\d+)"
        End If

    Set matches = re.Execute(s)

    If matches.Count > 0 Then
        For Each m In matches
            Debug.Print m.SubMatches(0)
        Next m
    End If

End Sub

Upvotes: 3

Related Questions