Asked by:
Using Arrays to count ages in worksheet
Question

I have a spreadsheet that includes ages of children enrolld in gymnastics classes. I would like to write a procedure that will process the age column adn total each age (i.e. there are 10 6yearolds taking classes, 8 7yearolds, etc.).
The code that I have come up with (admittedly after blowing a LOT of dust off of my limited VBA knowledge), is as follows:
Sub AgeSum()
Dim Ages(20) As Integer
Dim AgeCol As String
Dim Idx As Integer
Dim AgeVal As Integer
Dim ResTxtCol As String
Dim LoopIdx As Integer
Idx = 2
AgeCol = "F"
Sheets("Sheet1").Select
Do
AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value
Ages(AgeVal) = Ages(AgeVal) + 1
Idx = Idx + 1
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""
ResTxtCol = "G"
LoopIdx = 0
Idx = Idx + 2
For LoopIdx = 0 To UBound(Ages)
Sheets("Sheet1").Range(ResTxtCol & Idx).Value = "Count of children age LoopIdx:"
Sheets("Sheet1").Range(AgeCol & Idx).Value = Ages(LoopIdx)
Next
End SubWhen I try and run this, I get a type mismatch on the line
Ages(AgeVal) = Ages(AgeVal) + 1
So, what should I do? How can I cast the Ages array to be an integer that I can perform regular math operations on? Or didi I simply declare it improperly to begin with? (Maybe shouold have been a type Variant?)
Any advice/options you could give me would be greatly appreciiated.
Thanks
 Moved by Kee Poppy Tuesday, August 16, 2011 8:08 AM (From:Visual Basic Language)
Sunday, August 14, 2011 10:03 PM
All replies

Dim Idx As Integer
Dim AgeVal As Integer
Dim Ages As IntegerDo
AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value
Ages += AgeVal
Idx = Idx + 1
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""
KennethSunday, August 14, 2011 10:32 PM 
Or:
Dim Ages as New List(of Integer)
Dim AgeVal As Integer
Do
AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value
Ages.add( AgeVal )
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""
KennethSunday, August 14, 2011 10:34 PM 
Hi TechnoMage,
This topic would be better asked in Visual Basic Application forum. I will move this thread there for getting better support.
Thanks,
Kee Poppy [MSFT]
MSDN Community Support  Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Tuesday, August 16, 2011 8:07 AM 
Your code is most right (there are a few logic errors), therefore the type mismatch has another cause. Look at the data in the column F.
This are the corrected code:
Public Sub AgeSum() Dim Ages(20) As Integer Dim AgeCol As String Dim Idx As Integer Dim AgeVal As Integer Dim ResTxtCol As String Dim LoopIdx As Integer Idx = 2 AgeCol = "F" Sheets("Sheet1").Select Do AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value Ages(AgeVal) = Ages(AgeVal) + 1 Idx = Idx + 1 Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = "" ResTxtCol = "G" LoopIdx = 0 Idx = Idx + 2 For LoopIdx = 0 To UBound(Ages) Sheets("Sheet1").Range(ResTxtCol & (Idx + LoopIdx)).Value = "Count of children age " & LoopIdx Sheets("Sheet1").Range(AgeCol & (Idx + LoopIdx)).Value = Ages(LoopIdx) Next End Sub
please, mark this as answer if it is THE answer

Diego Cattaruzza
Microsoft MVP  Visual Basic: Development
blog: http://community.visualbasic.it/Diego
web site: http://www.visualbasic.itTuesday, August 16, 2011 8:38 AM 
I have a spreadsheet that includes ages of children enrolld in gymnastics classes. I would like to write a procedure that will process the age column adn total each age (i.e. there are 10 6yearolds taking classes, 8 7yearolds, etc.).
The code that I have come up with (admittedly after blowing a LOT of dust off of my limited VBA knowledge), is as follows:
Sub AgeSum()
Dim Ages(20) As Integer
Dim AgeCol As String
Dim Idx As Integer
Dim AgeVal As Integer
Dim ResTxtCol As String
Dim LoopIdx As Integer
Idx = 2
AgeCol = "F"
Sheets("Sheet1").Select
Do
AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value
Ages(AgeVal) = Ages(AgeVal) + 1
Idx = Idx + 1
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""
ResTxtCol = "G"
LoopIdx = 0
Idx = Idx + 2
For LoopIdx = 0 To UBound(Ages)
Sheets("Sheet1").Range(ResTxtCol & Idx).Value = "Count of children age LoopIdx:"
Sheets("Sheet1").Range(AgeCol & Idx).Value = Ages(LoopIdx)
Next
End SubWhen I try and run this, I get a type mismatch on the line
Ages(AgeVal) = Ages(AgeVal) + 1
So, what should I do? How can I cast the Ages array to be an integer that I can perform regular math operations on? Or didi I simply declare it improperly to begin with? (Maybe shouold have been a type Variant?)
Any advice/options you could give me would be greatly appreciiated.
Thanks
At the time of the fault, what are the values in the various variables? That should help debug the issue.Also, you may want to consider Excelnative tools for the job. Create a PivotTable or a Histogram or use the FREQUENCY function.
Tushar Mehta (Technology and Operations Consulting)
www.tusharmehta.com (Excel and PowerPoint addins and tutorials)
Microsoft MVP Excel 2000PresentTuesday, August 16, 2011 4:32 PM