Parsing formulas for cell references

bill

Well-known Member
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:

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

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Have a look at the Dependents property, for a completely different approach.

Have a look at the Dependents property, for a completely different approach.

I think that should be the Precedents property. But, unfortunately, that doesn't return external references (to another sheet). I was inclined to go with the ApplyNames method, but that also excludes external references.

Hmm, sounds interesting.

Perhaps i could find an empty cell on the source sheet (found by way of the '!'), dump the formula into it then look at 'Precedents'.

That could get nasty though and dump me into the fires of recursion.

I think I have the same issue. What if you simply want to set a variable equal to the array name in a vlookup. say =vlookup(a1,xyz,2,false)
make X = xyz so that the array can be referenced in a new formula.

what array name?

"xyz" I used the Define Name tool to name range A5:B10 "xyz". I use "xyz" in my lookup formula, instead of "A5:b10", to reference the range. Now I'd like to have a macro that pulls the name "xyz" out of the formula so that I can insert another lookup formula.

Here's a halfazz solution:
Code:
``````Option Explicit
___________________________________________________________________________________________
Private Sub ReplaceA1withNameRg()
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)
A1 = ""
For a = 1 To Len(formula)
c = Mid(formula, a, 1)

Select Case c
Case "=", ",", "(", ")", "<", ">", "=", "+", "/", "-", " "
theTest cl, formula, A1
Case Else
A1 = A1 & c
End Select
Next a
If Len(A1) > 0 Then
theTest cl, formula, A1
End If
End If
Next
End Sub
___________________________________________________________________________________________
Private Sub theTest(cl As Range, ByRef formula As String, ByRef A1 As String)
Dim nm As Name
Dim tg As Range

If InStr(1, A1, "DRS!") > 0 Then

For Each nm In DRS.Names
If InStr(1, nm.RefersTo, "!") > 0 Then
Set tg = DRS.Range(Mid(nm.RefersTo, 2))

If Mid(A1, InStr(1, A1, "!") + 1) = tg.Address(False, False) Then
Debug.Print cl.Address(False, False) & ": " & " Old: " & formula
Debug.Print cl.Address(False, False) & ": " & " New: " & Replace(formula, A1, nm.Name)
End If
End If
Next
End If
A1 = ""
End Sub``````

In place of DEBUG.PRINT's I'll instead change the value of the formula to the 'replaced' version (but of course that affects the loop so gotta be careful! - halfazz eh? )

Give me a couple days to digest.

This solutions DOES work for me but of course consider that, in my problem, all external references to other sheets derive from the DRS (codename) tab.

So, my solution is quite specific to my needs.

Replies
0
Views
243
Replies
3
Views
236
Replies
0
Views
259
Replies
5
Views
122
Replies
3
Views
235

1,219,905
Messages
6,150,902
Members
450,991
Latest member
ExcelDoer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back