What vba procedure can I use to count the number of formulas in a given range?

Sub Macro1()
Dim r As Range
Dim c As Integer
For Each r In Selection
If r.HasFormula Then
c = c + 1
End If
Next
MsgBox (c)
End Sub

Select the range on the worksheet and then run the macro
--
Gary's Student


"Sam" wrote:

What vba procedure can I use to count the number of formulas in a given range?

If you wanted to select the formula cells in a range manually, you could use:

Select the range
edit|Goto|special|check formulas

In code:

Option Explicit
Sub testme()

Dim myCount As Long

myCount = 0
On Error Resume Next
myCount = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas)). Cells.Count
On Error GoTo 0

MsgBox myCount

End Sub

The intersect() stuff is useful when there's only one cell selected.

Sam wrote:

What vba procedure can I use to count the number of formulas in a given range?

--

Dave Peterson

Perfect! Thanks!

"Gary''s Student" wrote:

Sub Macro1()
Dim r As Range
Dim c As Integer
For Each r In Selection
If r.HasFormula Then
c = c + 1
End If
Next
MsgBox (c)
End Sub

Select the range on the worksheet and then run the macro
--
Gary's Student


"Sam" wrote:

What vba procedure can I use to count the number of formulas in a given range?

Hi Sam,

What vba procedure can I use to count the number of formulas in
a given range?

Try:
'=============
Public Sub Tester()
Dim rng As Range
Dim i As Long

Set rng = Range("A1:D50") '<<==== CHANGE

On Error Resume Next
i = rng.SpecialCells(xlCellTypeFormulas).Count
On Error GoTo 0

MsgBox i

End Sub
'<<=============


---
Regards,
Norman

"Sam" wrote in message
...
What vba procedure can I use to count the number of formulas in a given
range?