locked
Using Arrays to count ages in worksheet RRS feed

  • 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 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 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
    Ages.add( AgeVal )
     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
    

     


    please, mark this as answer if it is THE answer
    ----------------
    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