ListBox column index count

[asyamonique]

Me.TextBox6.Value = .Sum(.Index(b, 3, 3))

Hello,
I need a code which will show up in the textbox the total count values of letter "S" at column 3
How can I alter given above code?
Thanks.

 

[Domenic]

The following code uses early binding, so you'll need to set a reference to the Microsoft Scripting Runtime library...

Visual Basic Editor (Alt+F11) >> Tools >> References >> and check/select Microsoft Scripting Runtime >> Click OK

The code also uses the Tag property of each textbox to identify the column and letter for the textbox, so for the Tag property of each one you'll need to enter the corresponding column number and letter in this format...

2;S  ----->  which means column 2, letter S

To access the Tag property for each textbox, right-click the textbox, and select Properties. Then, under Properties, enter the column number and letter in the above format. Here's the code...

Dim dicMain As Scripting.Dictionary             'main dictionary to hold column number and column dictionary
Dim dicColumn As Scripting.Dictionary           'column dictionary to hold criteria and count
Dim dicCurrent As Scripting.Dictionary          'currently referenced dictionary
Dim vColumns As Variant                         'column numbers for which to return counts
Dim vCriteria As Variant                        'criteria for which to return counts
Dim currentColumn As Long                       'currently referenced column
Dim strCurrentCriteria As String                'currently referenced criteria
Dim objCurrentControl As Control                'currently referenced ActiveX control

vColumns = Array(2, 4, 8)                       'columns for which to return counts
vCriteria = Array("S", "W", "J", "P")           'letters for which to return counts

'create an instance of a dictionary object for main dictionary
Set dicMain = New Scripting.Dictionary

'set dictinary to case-insensitive comparison mode
dicMain.CompareMode = TextCompare

'loop through each specified column
For i = 0 To UBound(vColumns)
    'create dictionary for column to hold criteria/count pair
    Set dicColumn = New Scripting.Dictionary
    'set to case-insensitive comparison mode
    dicColumn.CompareMode = TextCompare
    'initialize count for each criteria to zero
    For j = 0 To UBound(vCriteria)
        dicColumn(vCriteria(j)) = 0
    Next j
    'add column number and corresponding dictionary to main dictionary
    dicMain.Add Key:=vColumns(i), Item:=dicColumn
Next i

'loop through each row within listbox to return counts for specified columns
With ListBox1
    For i = 0 To .ListCount - 1
        For j = 0 To UBound(vColumns)
            currentColumn = vColumns(j)
            strCurrentCriteria = .List(i, currentColumn - 1)
            If Not IsError(Application.Match(strCurrentCriteria, vCriteria, 0)) Then
                Set dicCurrent = dicMain(currentColumn)
                dicCurrent(strCurrentCriteria) = dicCurrent(strCurrentCriteria) + 1
            End If
        Next j
    Next i
End With

'loop through each textbox and assign their respective counts
For Each objCurrentControl In Me.Controls
    If TypeName(objCurrentControl) = "TextBox" Then
        currentColumn = Split(objCurrentControl.Tag, ";")(0)
        strCurrentCriteria = Split(objCurrentControl.Tag, ";")(1)
        objCurrentControl.Value = dicMain(currentColumn)(strCurrentCriteria)
    End If
Next objCurrentControl

Hope this helps!

 

[asyamonique]

Thanks,
It helped me a lot.
Cheers

 

[asyamonique]

Hello Again,
Your instruction helped me a lot...
Is there any way to get total values of that counts like "4;S+4;P+4;J+4;W" into the another text box at the bottom.
How can I format that textboxes?

Sample:

P: 2
S: 3
J: 4
W:5

TOT: 14