Counting digits in a range up to a max value

[gerald8000]

How can I count the number of didgits in a range up to a max value?

If Cell A1 had a value of 23. How can I calculate the B3:B12?

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]23
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Number of 1's (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21)
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Number of 2's (2, 12, 22) =
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Number of 3's (3, 13, 23) =
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Number of 4's (4, 14) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Number of 5's (5, 15) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Number of 6's (6, 16) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Number of 7's (7, 17) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Number of 8's (8, 18) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Number of 9's (9, 19) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Number of 0's (10, 20) =
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi,

Not sure I understand your expected results. Why don't, for example, 21 and 23 count for Number of 2s? And haven't you given 12 results for Number of 1s, not 13?

Regards

 

[gerald8000]

Hi,

Lets use the 1's as and example. All we are doing is counting the number of 1's leading up to the number 23.

Number of 1's (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21)

There are 12 numbers listed in brackets that include a "1" but "11" has two "1"'s so the total is 13.

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG13Oct06
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
[COLOR="Navy"]For[/COLOR] n = 1 To [a1]
    nStr = nStr & n
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] n = 1 To Len(nStr)
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Mid(nStr, n, 1)) [COLOR="Navy"]Then[/COLOR]
       Dic.Add (Mid(nStr, n, 1)), 1
    [COLOR="Navy"]Else[/COLOR]
        Dic(Mid(nStr, n, 1)) = Dic(Mid(nStr, n, 1)) + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Range("A3").Resize(Dic.Count, 2) = Application.Transpose(Array(Dic.Keys, Dic.items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[gerald8000]

Hi MickG,

To be honest, never done code in Excel before, how do I get this to function in a cell?

Thanks for your time.

 

[Veritan]

MickG's method will definitely work, though you might have to give VBA access to the Scripting Runtime library. If you'd like to do it without having to go that route, try this. Get into the VB Editor by pressing Alt + F11. Then click Insert --> Module. Copy this code into the blank module window that opens and press the Run button (looks like a green play arrow). You should now be set!

Sub CountNumbers()
    Dim i&, x%, Count&, WriteRange, SearchNum$, SearchStart%
    
    Application.ScreenUpdating = False
    For x = 0 To 9
        For i = 1 To [A1]
            SearchNum = i
            For SearchStart = 1 To Len(SearchNum)
                If Mid(i, SearchStart, 1) = x Then Count = Count + 1
            Next SearchStart
        Next i
        Set WriteRange = Range("A3:A12").Find(x)
        WriteRange.Offset(, 1) = Count
        Count = 0
    Next x
    Application.ScreenUpdating = True
End Sub

 

[XOR LX]

Hi,

Lets use the 1's as and example. All we are doing is counting the number of 1's leading up to the number 23.

Number of 1's (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21)

There are 12 numbers listed in brackets that include a "1" but "11" has two "1"'s so the total is 13.

Thanks, understood. But what about my other question?

Regards

 

[MickG]

Try this:-
NB:- Make sure value is in "A1"

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

 

[gerald8000]

Hi Veritan,

I did as you said on a blank sheet but when I try to run the script I get the following error:


-- removed inline image ---


?

 

[gerald8000]

Hi Veritan,

I did as you said on a blank sheet but when I try to run the script I get the following error:

Run-time error 91, Object variable or With block variable not set?