How many formula
 

Hi all,

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

Many thanks

Andy

How many formula
 

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 "This workbook has " & Count & " formulas"

End Sub

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

--

Regards,

Peo Sjoblom


"Andy the yeti" wrote in message
...
Hi all,

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

Many thanks

Andy

How many formula
 

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 "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy the yeti" wrote in
message
...
Hi all,

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

Many thanks

Andy

How many formula
 

thank you both !

"Chip Pearson" wrote:

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 "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy the yeti" wrote in
message
...
Hi all,

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

Many thanks

Andy

How many formula
 

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 '<-- added
WSCount = WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
TotalCount = TotalCount + WSCount
Next WS
MsgBox "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub


Chip Pearson wrote:

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 "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Andy the yeti" wrote in
message
...
Hi all,

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

Many thanks

Andy

--

Dave Peterson

How many formula
 

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


"Dave Peterson" wrote in message
...
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 '<-- added
WSCount =
WS.UsedRange.SpecialCells(xlCellTypeFormulas).Coun t
TotalCount = TotalCount + WSCount
Next WS
MsgBox "You have " & Format(TotalCount, "#,##0") & " formulas."
End Sub


Chip Pearson wrote:

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 "You have " & Format(TotalCount, "#,##0") & "
formulas."
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Andy the yeti" wrote
in
message
...
Hi all,

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

Many thanks

Andy

--

Dave Peterson