Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting numbers and letters
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting numbers and letters
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 "Sean" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting numbers and letters
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. Sean wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Formating Letters to Numbers while Displaying Letters | Excel Worksheet Functions | |||
Columns now numbers rather than letters how do i get letters back | Excel Discussion (Misc queries) | |||
Counting how many numbers and letters appear in a particular row | Excel Discussion (Misc queries) | |||
Counting occurance of letters or numbers | Excel Discussion (Misc queries) | |||
Counting groups of exact case numbers w/letters in them. | Excel Discussion (Misc queries) |