Monday, August 07, 2006
Count cells without formulae
If you want to count cells in a given range without formulae use this function
If you want to count cells with formule then just remove then "Not" in front of c.HasFormula
Function CountNoFormula(rng As Range) As Long
Dim c As Range
On Error GoTo CountNoFormula_Error
For Each c In rng
If Not c.HasFormula Then
CountNoFormula = CountNoFormula + 1
End If
Next c
CountNoFormula_CleanUpExit:
Exit Function
CountNoFormula_Error:
CountNoFormula = CVErr(Err.Number)
Resume CountNoFormula_CleanUpExit
End Function
Dim c As Range
On Error GoTo CountNoFormula_Error
For Each c In rng
If Not c.HasFormula Then
CountNoFormula = CountNoFormula + 1
End If
Next c
CountNoFormula_CleanUpExit:
Exit Function
CountNoFormula_Error:
CountNoFormula = CVErr(Err.Number)
Resume CountNoFormula_CleanUpExit
End Function
If you want to count cells with formule then just remove then "Not" in front of c.HasFormula
