Change UDF to work as array formula
Great. Thank you.
Jan
Dave Peterson wrote:
How about just selecting 10 contiguous cells (10 rows x 1 column or 1
row by 10 columns) and have the count of the numbers returned into
that range.
Option Explicit
Function NDIG(myStr As String) As Variant
Dim cif(0 To 9) As Long
Dim iCtr As Long
Dim myChar As String
For iCtr = 1 To Len(myStr)
myChar = Mid(myStr, iCtr, 1)
If IsNumeric(myStr) Then
cif(CLng(myChar)) = cif(CLng(myChar)) + 1
End If
Next iCtr
If Application.Caller.Rows.Count = 10 _
And Application.Caller.Columns.Count = 1 Then
NDIG = Application.Transpose(cif)
ElseIf Application.Caller.Rows.Count = 1 _
And Application.Caller.Columns.Count = 10 Then
NDIG = cif
Else
NDIG = CVErr(xlErrRef)
End If
End Function
Select b1:B10 and array enter (ctrl-shift-enter) your formula:
=NDIG(a1)
Jan Kronsell wrote:
I have the following UDF. It count the number of any digit in a cell
Function NDIG(ce As String, no As Byte) As Variant
Dim cif(9) As Long
For i = 1 To Len(ce)
Select Case CByte(Mid(ce, i, 1))
Case Is = 0
cif(0) = cif(0) + 1
Case Is = 1
cif(1) = cif(1) + 1
Case Is = 2
cif(2) = cif(2) + 1
Case Is = 3
cif(3) = cif(3) + 1
Case Is = 4
cif(4) = cif(4) + 1
Case Is = 5
cif(5) = cif(5) + 1
Case Is = 6
cif(6) = cif(6) + 1
Case Is = 7
cif(7) = cif(7) + 1
Case Is = 8
cif(8) = cif(8) + 1
Case Is = 9
cif(9) = cif(9) + 1
End Select
Next
NDIG = cif(no)
End Function
I use i by typing the number I want counted in a1 and the numbers
fro, 0 to 9 in B1:B10. I then enter the UDF in C1 like
=NDIG($A$1;B1) and copy down to C10, and it retunrs the number of
any digit in the cell.
Now I wonder if the UDF can be changes, so I can enter it as an array
formula in C1:C10 in stead of having to copy it?
Jan
|