View Full Version : count a numberformat
lior03
12-19-2006, 01:11 PM
hello
i wanted to count the number of times a certain numberformat appear in a range.i came up with:
Function nnumberformat(r As Range, f As String)
Dim cell
For Each cell In r
If cell.numberformat = f Then
nnumberformat = cell.Value + 1
End If
Next
End Function
can anyone help?
thanks
mdmackillop
12-19-2006, 01:28 PM
Function nnumberformat(r As Range, f As String)
Dim cell
For Each cell In r
If cell.NumberFormat = f Then
nnumberformat = nnumberformat + 1
End If
Next
End Function
Bob Phillips
12-19-2006, 01:29 PM
Function nnumberformat(r As Range, f As String)
Dim cell
For Each cell In r
If cell.NumberFormat = f Then
nnumberformat = nnumberformat + 1
End If
Next
End Function
but it won't auto-recalculate when a cell changes its format.
mdmackillop
12-19-2006, 01:33 PM
SNAP!!!
JimmyTheHand
12-19-2006, 01:37 PM
It's not recommended to use "numberformat" as a function name because "NumberFormat" is a cell property. I think your code would be more like this:
Function NRFormat(r As Range, f As String) as Long
Dim cell As Range, Result As Long
Result = 0
For Each cell In r.Cells
If cell.numberformat = f Then
Result = Result + 1
End If
Next
NRFormat = Result
End Function
Also, you should choose more descriptive variable names than r and f. It will help later when you want to use this function on a worksheet, for example.
Jimmy
Edit:
I'm the laughing 3rd one :rotlaugh:
Bob Phillips
12-19-2006, 02:01 PM
It's not recommended to use "numberformat" as a function name because "NumberFormat" is a cell property.
He didn't, he had two n's, nnumberformat.
JimmyTheHand
12-19-2006, 02:23 PM
He didn't, he had two n's, nnumberformat.
Sir, Yes, Sir !!! :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.