Hi,
I was given a large spreadsheet - full of formulas.
(Most of those formulas are identical - copied down a range of cells.)

I need to extract each "unique" formula, and my first attempt was to convert
the formula to a string and copy it to an empty sheet, then to analyze it.
(See code below.)

Is there a better way to get the formulas used in a worksheet?

Thanks for your help.
Regards
Habib
------------------------------------
For colz = 4 To 76
Application.StatusBar = "Reading Col " & colz
For rowz = 2 To 60957
Worksheets("Sheet1").Cells(rowz, colz).Select
If Left(ActiveCell.Formula, 1) = "=" Then
Worksheets("Sheet2").Cells(rowz, colz).Value = "'" &
CStr(ActiveCell.Formula)
End If
ActiveCell.Offset(1, 0).Select
Next
ActiveCell.Offset(0, 1).Select
Application.StatusBar = "Reading Col " & colz

Next
Application.StatusBar = "Done"



--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 24

--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.

Try this code to see if it helps



Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count,
"0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

' Adjust column widths
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub



--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=537881

Just a heads up, but
If your really checking D4:BO60957

there is a good chance that this command

Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)


will only return a 8192 area subset of the real areas that contain formulas.
You may want to work in smaller chunks if it is a problem. .

code like

With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

does is incorrect. It should have leading periods

With FormulaSheet
.Range("A1") = "Address"
.Range("B1") = "Formula"
.Range("C1") = "Value"
.Range("A1:C1").Font.Bold = True
End With

To actually use the With statement. It may have been stripped by the mail
software although they are missing in the Excel forum as well.



--
Regards,
Tom Ogilvy


"VBA Noob" wrote in
message ...

Try this code to see if it helps



Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count,
"0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

' Adjust column widths
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub



--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=537881

Noob and Tom,
Thanks for your replies. I'll try your code in a bit.
What is the limit on formula length?
Regards
Habib

--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21

--------------------------------------------

Share your knowledge. Add your favorite questions and answers


Help add questions to this site! We want Your input.
"Tom Ogilvy" wrote in message
...
Just a heads up, but
If your really checking D4:BO60957

there is a good chance that this command

Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)


will only return a 8192 area subset of the real areas that contain
formulas.
You may want to work in smaller chunks if it is a problem. .

code like

With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

does is incorrect. It should have leading periods

With FormulaSheet
.Range("A1") = "Address"
.Range("B1") = "Formula"
.Range("C1") = "Value"
.Range("A1:C1").Font.Bold = True
End With

To actually use the With statement. It may have been stripped by the
mail
software although they are missing in the Excel forum as well.



--
Regards,
Tom Ogilvy


"VBA Noob" wrote
in
message ...

Try this code to see if it helps



Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count,
"0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

' Adjust column widths
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub



--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread:
http://www.excelforum.com/showthread...hreadid=537881