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