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
--
Dave Peterson
|
Similar Threads
