Finding members in a formula

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Finding members in a formula

Post by bknight »

I have a spreadsheet that contains errors in some formulas and I was looking for code to parse those formulas.
After about an hour of inspecting each formula and writing it down albeit on a word processor, I was able to identify the two formulas that were incorrect.
Then I attempted to write code to parse each cells formula and output the formula contained, that failed miserably after maybe 2-3 hours' worth of iterations. Is there a code to parse formulas into a Db such that the pieces can be sorted to find errors more easily?
the column that contains the formulas have those formulas in the form of
1. Blanks
2. =cell(row,column) + cell(row+1,column)
3. =cell(row, column) + cell(row+1, column) + cell(row+2, column) + cell(row +y, column)
Where the column doesn't change, the row may or may not increment by 1 even though the spreadsheet has continuous data row after row, but the formulas are NOT necessarily row after row.
:scratch:

User avatar
HansV
Administrator
Posts: 72573
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding members in a formula

Post by HansV »

What would you like the output to look like?

(I assume that you're aware that you can show formulas instead of their results by clicking 'Show Formulas' in the 'Formula Auditing' group of the Formulas tab of the ribbon)
Regards,
Hans

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Re: Finding members in a formula

Post by bknight »

Well, I wouldn't need the = sign or the + sign but something like this
G5 O5(Cell in which formula exists)
G6
G8 O8(Cell in which formula exists)
G11

Text tabular form, I could then sort them to visually inspect. Unless some other form which you believe to be better suited.

User avatar
HansV
Administrator
Posts: 72573
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding members in a formula

Post by HansV »

You could run this macro. Make sure that the sheet with the formulas is the active sheet when you run the macro.
It will produce a new workbook with the cell addresses and formulas.

Code: Select all

Sub ListFormulas()
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim rng As Range
    Dim a(1 To 100000, 1 To 2) As String
    Dim r As Long
    Application.ScreenUpdating = False
    Set wshS = ActiveSheet
    Set wbkT = Workbooks.Add(xlWBATWorksheet)
    Set wshT = wbkT.Worksheets(1)
    a(1, 1) = "Cell"
    a(1, 2) = "Formula"
    r = 1
    For Each rng In wshS.Cells.SpecialCells(xlCellTypeFormulas)
        r = r + 1
        If r > 100000 Then
            Beep
            Exit For
        End If
        a(r, 1) = rng.Address(False, False)
        a(r, 2) = "'" & rng.Formula
    Next rng
    wshT.Range("A1:B100000").Value = a
    wshT.Range("A1:B1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub
Regards,
Hans

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Re: Finding members in a formula

Post by bknight »

I didn't get a message about a new post, so late.
The macro worked until

wshT.Range("A1:B100000").Value = a

Run time error 1004
Method 'range of object'_Worksheet failed.

User avatar
HansV
Administrator
Posts: 72573
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding members in a formula

Post by HansV »

Is your workbook an Excel 97-2003 workbook (extension .xls)?
Regards,
Hans

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Re: Finding members in a formula

Post by bknight »

Yes

User avatar
HansV
Administrator
Posts: 72573
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding members in a formula

Post by HansV »

Change all occurrences of 100000 to 65000
Regards,
Hans

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Re: Finding members in a formula

Post by bknight »

That works but the formulas need to be parsed. The first parsing is easy but I having some issues with the second.
=MID(B2,2,FIND("+",B2,2)-2) gives the first cell in the equations.
=FIND("+",B2,FIND("+",B2,2)+1) was my first attempt but this resulted in #VALUE!
That seems to occur if there are no further "+s" in for example =G6+G7, it works in for example =G154+G155+G156, but the #VALUE! is useless for my evaluation

NM I just took the right function =RIGHT(B2,LEN(B2)-FIND("+",B2,2)) and this worked in all but two cases.

bknight
5StarLounger
Posts: 687
Joined: 08 Jul 2016, 18:53

Re: Finding members in a formula

Post by bknight »

Well that was unpleasant, but the errors were both omission of cells and duplication of cells.
Thanks for your help.

User avatar
HansV
Administrator
Posts: 72573
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding members in a formula

Post by HansV »

Glad you were able to solve it!
Regards,
Hans