Cell formula

[HSalim[MVP]]

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"

 

[VBA Noob]

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

 

[Tom Ogilvy]

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.

 

[HSalim[MVP]]

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.