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"



--
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.

[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.



--
Regards,
Tom Ogilvy


"VBA Noob" <VBA.Noob.275ecd_1146519318.0528@excelforum-nospam.com> wrote in
message news:VBA.Noob.275ecd_1146519318.0528@excelforum-nospam.com...
>
> 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
>

[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.
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uUxolqWbGHA.724@TK2MSFTNGP05.phx.gbl...
> 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" <VBA.Noob.275ecd_1146519318.0528@excelforum-nospam.com> wrote
> in
> message news:VBA.Noob.275ecd_1146519318.0528@excelforum-nospam.com...
>>
>> 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
>>
>
>