Hello,
Wondering if our loving community has a way to parse a formula that will extract cell reference (or even named references).
For example, with the following:
=IF(DRS!K106<0,-DRS!K106,0)
I'd like to pull out this element:
DRS!K106
or in this formula:
=ABS(DRS!K58+DRS!K60)
I'd like to pull out
DRS!K58
DRS!K60
The true objective is to then scour (in this case) the DRS tab for a Named Range and then later to replace the relative (or absolute) references for the named range version.
It seems relatively easy (at least from the example above) to parse the string by "(", ")", "<", ">", "=", "+", "/", "-", " " and anything else would be considered a candidate for a cell reference.
I'd then look for a 'DRS!' in the string and if found would know that I have an offpage reference. For that matter I could look for a '!' and the scour all my worksheets for what ever preceeded the bang.
My code looks thus far:
It does appear to parse out cell references OK but wondering if there is a better way.
(I'llbe adding new characters to the SELECT CASE as I 'discover' them
Your thoughs?
Wondering if our loving community has a way to parse a formula that will extract cell reference (or even named references).
For example, with the following:
=IF(DRS!K106<0,-DRS!K106,0)
I'd like to pull out this element:
DRS!K106
or in this formula:
=ABS(DRS!K58+DRS!K60)
I'd like to pull out
DRS!K58
DRS!K60
The true objective is to then scour (in this case) the DRS tab for a Named Range and then later to replace the relative (or absolute) references for the named range version.
It seems relatively easy (at least from the example above) to parse the string by "(", ")", "<", ">", "=", "+", "/", "-", " " and anything else would be considered a candidate for a cell reference.
I'd then look for a 'DRS!' in the string and if found would know that I have an offpage reference. For that matter I could look for a '!' and the scour all my worksheets for what ever preceeded the bang.
My code looks thus far:
Code:
Private Sub ReplaceA1withNameR()
Dim cl As Range
Dim rg As Range
Dim a As Integer ' a for array position since a string is really an array:)
Dim c As String
Dim formula As String
Dim A1 As String
Set rg = Selection
For Each cl In rg
If Left(cl.formula, 1) = "=" Then
formula = Mid(cl.formula, 2)
For a = 1 To Len(formula)
c = Mid(formula, a, 1)
Select Case c
Case ",", "(", ")", "<", ">", "=", "+", "/", "-", " "
If InStr(1, A1, "DRS!") > 0 Then
Debug.Print cl.Address(False, False) & ": " & A1
End If
A1 = ""
Case Else
A1 = A1 & c
End Select
Next a
If Len(A1) > 0 Then
If InStr(1, A1, "DRS!") > 0 Then
Debug.Print cl.Address(False, False) & ": " & A1
End If
A1 = ""
End If
End If
Next
End Sub
It does appear to parse out cell references OK but wondering if there is a better way.
(I'llbe adding new characters to the SELECT CASE as I 'discover' them
Your thoughs?