Databases
StackOverflow2013
Postsdbo
POVBA Excel Counting Specific Values
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Excel Counting Specific Values
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJesse Smothermon
    1. USJesse Smothermon
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA Excel Counting Specific Values
      1. USJohn M
      1. VTFavorite
    2. VO
      singulars
      1. POVBA Excel Counting Specific Values
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you have to do this programmatically? What about using pivot tables?
      singulars
      1. POVBA Excel Counting Specific Values
      1. USsteinar
    2. COThis code is part of a button macro I'm creating. I have to admit I'm not entirely sure what a pivot table is but I'll go look that up
      singulars
      1. POVBA Excel Counting Specific Values
      1. USJesse Smothermon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload