title: VBA Excel Counting Specific Values
body: <p>I'm trying to write a program that will loop through cells of a specific column (assigned by the user), find new values in those cells and count how many times a specific value is found.  The main problem I'm having right now is that this is hard-coded like below:</p>
  
  <pre><code>Function findValues() As Long
  For iRow = 2 To g_totalRow
      If (ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text = "") Then
          nullInt = nullInt + 1
      ElseIf (someValue1 = "" Or someValue1 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue1 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt1 = someInt1 + 1
      ElseIf (someValue2 = "" Or someValue2 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue2 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt2 = someInt2 + 1
      ElseIf (someValue3 = "" Or someValue3 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue3 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt3 = someInt3 + 1
      ElseIf (someValue4 = "" Or someValue4 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue4 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt4 = someInt4 + 1
      ElseIf (someValue5 = "" Or someValue5 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue5 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt5 = someInt5 + 1
      ElseIf (someValue6 = "" Or someValue6 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue6 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt6 = someInt6 + 1
      ElseIf (someValue7 = "" Or someValue7 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue7 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt7 = someInt7 + 1
      ElseIf (someValue8 = "" Or someValue8 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue8 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt8 = someInt8 + 1
      ElseIf (someValue9 = "" Or someValue9 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue9 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt9 = someInt9 + 1
      ElseIf (someValue10 = "" Or someValue10 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text) Then
          someValue10 = ActiveWorkbook.Sheets(sheetName).Cells(iRow, iCol).Text
          someInt10 = someInt10 + 1
      End If
  Next iRow
  End Function
  </code></pre>
  
  <p>Here, if the ActiveCell is blank then the nullInt will get incremented, if the ActiveCell has some value then it'll find which of the variables has that same value or the ActiveCell value will be assigned to one of the variables.  I created ten variables strictly for testing purposes but I need to make up to one hundred.  I was wondering if there was a way to complete this quickly.  The only way I could think of was to create a String array and an Int array and store the values that way.  However I'm not sure if this is the best way to get this done.</p>
  
  <p><strong>Edit</strong>
  This portion is directed specifically to dictionaries.  Say there is a specific column titled "State".  This contains the 50 North American states.  Some of these states are repeated and there is a total of 800 values in this column.  How do I keep track of how many times (for example) Texas gets hit?</p>
  
  <p>Thank you,</p>
  
  <p>Jesse Smothermon</p>