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.
Finding members in a formula
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding members in a formula
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)
(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)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Finding members in a formula
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.
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.
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding members in a formula
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.
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Finding members in a formula
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.
The macro worked until
wshT.Range("A1:B100000").Value = a
Run time error 1004
Method 'range of object'_Worksheet failed.
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding members in a formula
Is your workbook an Excel 97-2003 workbook (extension .xls)?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Finding members in a formula
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.
=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.
-
- BronzeLounger
- Posts: 1379
- Joined: 08 Jul 2016, 18:53
Re: Finding members in a formula
Well that was unpleasant, but the errors were both omission of cells and duplication of cells.
Thanks for your help.
Thanks for your help.
-
- Administrator
- Posts: 78446
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands