# 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 6-year-olds taking classes, 8 7-year-olds, 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 Sub

When 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 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 Integer

Do
AgeVal = Sheets("Sheet1").Range(AgeCol & Idx).Value
Ages += AgeVal
Idx = Idx + 1
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""

Kenneth
Sunday, 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
Loop Until Sheets("Sheet1").Range(AgeCol & Idx).Value = ""

Kenneth
Sunday, 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
```

----------------
Diego Cattaruzza
Microsoft MVP - Visual Basic: Development
blog: http://community.visual-basic.it/Diego
web site: http://www.visual-basic.it
Tuesday, 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 6-year-olds taking classes, 8 7-year-olds, 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 Sub

When 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 Excel-native tools for the job.  Create a PivotTable or a Histogram or use the FREQUENCY function.

Tushar Mehta (Technology and Operations Consulting)
www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
Microsoft MVP Excel 2000-Present
Tuesday, August 16, 2011 4:32 PM