PDA

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

xld
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:

xld
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 !!! :)