Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change UDF to work as array formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change UDF to work as array formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't seem to get my Array formula to work................ | Excel Discussion (Misc queries) | |||
VLOOKUP does not work with array formula | Excel Worksheet Functions | |||
Array formula in a makro want work | Excel Programming | |||
array formula does not work | Excel Worksheet Functions | |||
Will an Array Formula work here? | Excel Discussion (Misc queries) |