software

Hi all,

Is there a way to count up how many formulae there are in workbook across
all the tabs?

Many thanks

Andy

For a single sheet yes, press F5, select special then formulas, now right
click in the status bar to the far right and select count. Otherwise you
need to use a macro

************************************************** ****
Option Explicit

Sub CountFormula()

Dim sh As Worksheet
Dim cell As Range
Dim Count As Integer

For Each sh In ActiveWorkbook.Sheets
For Each cell In sh.UsedRange
If cell.HasFormula Then
Count = Count 1
End If
Next cell
Next sh

MsgBox quot;This workbook has quot; amp; Count amp; quot; formulasquot;

End Sub

************************************************** ***

--

Regards,

Peo Sjoblomquot;Andy the yetiquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; Is there a way to count up how many formulae there are in workbook across
gt; all the tabs?
gt;
gt; Many thanks
gt;
gt; Andy
Only with VBA code.

Sub CountFormulas()
Dim TotalCount As Long
Dim WSCount As Long
Dim WS As Worksheet
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
TotalCount = TotalCount WSCount
Next WS
MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot; formulas.quot;
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;Andy the yetiquot; gt; wrote in
message
...
gt; Hi all,
gt;
gt; Is there a way to count up how many formulae there are in
gt; workbook across
gt; all the tabs?
gt;
gt; Many thanks
gt;
gt; Andy
thank you both !

quot;Chip Pearsonquot; wrote:

gt; Only with VBA code.
gt;
gt; Sub CountFormulas()
gt; Dim TotalCount As Long
gt; Dim WSCount As Long
gt; Dim WS As Worksheet
gt; On Error Resume Next
gt; For Each WS In ActiveWorkbook.Worksheets
gt; WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
gt; TotalCount = TotalCount WSCount
gt; Next WS
gt; MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot; formulas.quot;
gt; End Sub
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;Andy the yetiquot; gt; wrote in
gt; message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Is there a way to count up how many formulae there are in
gt; gt; workbook across
gt; gt; all the tabs?
gt; gt;
gt; gt; Many thanks
gt; gt;
gt; gt; Andy
gt;
gt;
gt;

I think there's a slight bug in this.

If the worksheet doesn't have a formula, wscount retains its value from the
previous count.

Option Explicit

Sub CountFormulas()
Dim TotalCount As Long
Dim WSCount As Long
Dim WS As Worksheet
On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
WSCount = 0 'lt;-- added
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
TotalCount = TotalCount WSCount
Next WS
MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot; formulas.quot;
End SubChip Pearson wrote:
gt;
gt; Only with VBA code.
gt;
gt; Sub CountFormulas()
gt; Dim TotalCount As Long
gt; Dim WSCount As Long
gt; Dim WS As Worksheet
gt; On Error Resume Next
gt; For Each WS In ActiveWorkbook.Worksheets
gt; WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
gt; TotalCount = TotalCount WSCount
gt; Next WS
gt; MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot; formulas.quot;
gt; End Sub
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt; quot;Andy the yetiquot; gt; wrote in
gt; message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; Is there a way to count up how many formulae there are in
gt; gt; workbook across
gt; gt; all the tabs?
gt; gt;
gt; gt; Many thanks
gt; gt;
gt; gt; Andy

--

Dave Peterson

Dave,

You're right. WSCount needs to be reset to zero for each sheet.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;Dave Petersonquot; gt; wrote in message
...
gt;I think there's a slight bug in this.
gt;
gt; If the worksheet doesn't have a formula, wscount retains its
gt; value from the
gt; previous count.
gt;
gt; Option Explicit
gt;
gt; Sub CountFormulas()
gt; Dim TotalCount As Long
gt; Dim WSCount As Long
gt; Dim WS As Worksheet
gt; On Error Resume Next
gt; For Each WS In ActiveWorkbook.Worksheets
gt; WSCount = 0 'lt;-- added
gt; WSCount =
gt; WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
gt; TotalCount = TotalCount WSCount
gt; Next WS
gt; MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot; formulas.quot;
gt; End Sub
gt;
gt;
gt; Chip Pearson wrote:
gt;gt;
gt;gt; Only with VBA code.
gt;gt;
gt;gt; Sub CountFormulas()
gt;gt; Dim TotalCount As Long
gt;gt; Dim WSCount As Long
gt;gt; Dim WS As Worksheet
gt;gt; On Error Resume Next
gt;gt; For Each WS In ActiveWorkbook.Worksheets
gt;gt; WSCount =
gt;gt; WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
gt;gt; TotalCount = TotalCount WSCount
gt;gt; Next WS
gt;gt; MsgBox quot;You have quot; amp; Format(TotalCount, quot;#,##0quot;) amp; quot;
gt;gt; formulas.quot;
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Cordially,
gt;gt; Chip Pearson
gt;gt; Microsoft MVP - Excel
gt;gt; Pearson Software Consulting, LLC
gt;gt; www.cpearson.com
gt;gt;
gt;gt; quot;Andy the yetiquot; gt; wrote
gt;gt; in
gt;gt; message
gt;gt; ...
gt;gt; gt; Hi all,
gt;gt; gt;
gt;gt; gt; Is there a way to count up how many formulae there are in
gt;gt; gt; workbook across
gt;gt; gt; all the tabs?
gt;gt; gt;
gt;gt; gt; Many thanks
gt;gt; gt;
gt;gt; gt; Andy
gt;
gt; --
gt;
gt; Dave Peterson