Counting numbers and letters

[Sean]

I'm looking for a script that will count how many times a number or letter
occurs. It would be beneficial if it could give me a total of all the
numbers 0-9 and all the letters A-Z. For instance...
If I set my cells up like such...
8A-1983
4A-1988
4A-1990
etc...

I want the script to search the entire active sheet or selection and return
a new sheet with something like...

0-1
1-3
2-
3-1
4-2
5-
6-
7-
8-4
9-4

a-3
b-
c-
....
Leaving the value for any zeros as a blank next to the owner.
Thanks in advance.
--
______________________________
Thank you, Sean
Artist/Production Manager
Please visit us at www.oatesflag.com
502-267-8200
502-267-8246 fax

 

[Stephen Wortley]

Hi Sean,

I'm afraid I can't provide a complete code solution to this, but a few
ideas on how to put it together - I'm sure one of the other guys will
give you the details

1) Set up a range that contains your data
2) Use a find loop for each character, setting up a counter variable
ditching the results into a 2d array (a,1), (b,13), ... (9,14)

or perhaps you could do something along the lines of

For Lengt = 1 to Len(ActiveCell)
Select Case Right(Left(ActiveCell,i),1)
Case is = "a"
a = a + 1
Case is = "b"
b = b + 1
.....

Next Lengt
Then step through all filled cells

Any other ideas guys?

Best of Luck

Stephen Wortley
Derivatives Operations
State Street Edinburgh

 

[Dave Peterson]

First, you could use a worksheet formula to get any of these values:

=SUM(LEN(upper(A1:G99))-LEN(SUBSTITUTE(upper(A1:G99),"0","")))
But hit ctrl-shift-enter instead of just enter. If you do it correctly, excel
will wrap curly brackets {} around your formula. (don't type them yourself.)

And you use A and a interchangeably. Substitute is case sensitive--so using
Upper() will make them the same.

As code:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myChars(1 To 36) As String
Dim iCtr As Long
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim myFormula As String

'0 to 9
For iCtr = 1 To 10
myChars(iCtr) = CStr(iCtr - 1)
Next iCtr

'A to Z
For iCtr = 11 To 36
myChars(iCtr) = Chr(65 + iCtr - 11)
Next iCtr

Set curWks = ActiveSheet

With curWks
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, .UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "please select some cells in the used range!"
Exit Sub
End If

Set newWks = Worksheets.Add
newWks.Range("a1").Resize(1, 2).Value _
= Array("Value", "Count")

oRow = 2
For iCtr = LBound(myChars) To UBound(myChars)
newWks.Cells(oRow, "A").Value = myChars(iCtr)
myFormula = "sum(len(upper(" & _
myRng.Address(external:=True) & "))" _
& "-len(substitute(upper(" & _
myRng.Address(external:=True) _
& "),""" & myChars(iCtr) & ""","""")))"

newWks.Cells(oRow, "B").Value _
= .Evaluate(myFormula)

oRow = oRow + 1
Next iCtr
End With

End Sub

Select your range before you run this.