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]

What has b been assigned? If b has been assigned a range object, try...

Me.TextBox6.Value = .CountIf(.Index(b, 0, 3), "S")

 

[asyamonique]

Dim a, i As Long, ii As Long, b(), n As LongListBox1.Clear
With ComboBox1
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("list").Range("a:a"), .Text) = 0 Then


Exit Sub
End If
a = Worksheets("list").Range("a1").Resize(Worksheets("list").Range("a" & Rows.Count).End(xlUp).Row, 37).Value
For i = 1 To UBound(a, 1)
If a(i, 1) = .Text Then
n = n + 1: ReDim Preserve b(1 To 37, 1 To n)
For ii = 1 To UBound(a, 2)
b(ii, n) = a(i, ii)
Next
End If
Next
End With
With ListBox1
.ColumnCount = 37
.ColumnWidths = "0;0;150;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;0;0;0"
.Column = b
End With
With Application
Me.TextBox6.Value = .CountIf(.Index(b, 0, 3), "S")
End With

It did not give the result.
Thanks

 

[Norie]

Is the column you want to count actually the 3rd column in the listbox or is it the 3rd visible column in the listbox?

 

[asyamonique]

3rd visible column
I will do the same for other visible columns as well.

 

[asyamonique]

Dim a, i As Long, ii As Long, b(), n As LongListBox1.Clear
With ComboBox1
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("list").Range("a:a"), .Text) = 0 Then


Exit Sub
End If
a = Worksheets("list").Range("a1").Resize(Worksheets("list").Range("a" & Rows.Count).End(xlUp).Row, 36).Value
For i = 1 To UBound(a, 1)
If a(i, 1) = .Text Then
n = n + 1: ReDim Preserve b(1 To 36, 1 To n)
For ii = 1 To UBound(a, 2)
b(ii, n) = a(i, ii)
Next
End If
Next
End With
With ListBox1
.ColumnCount = 36
.ColumnWidths = "0;0;150;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;0;0"
.Column = b
End With
With Application
Me.TextBox6.Value = .CountIf(.Index(b, 0, 3), "S")
End With

Having on textbox6 as zero result!

 

[Domenic]

You say that you want the 3rd visible column. If so, that would mean that you want this column in red...

[COLOR=#333333].ColumnWidths = "0;0;150;42;[/COLOR][COLOR=#ff0000]42[/COLOR][COLOR=#333333];42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;0;0"[/COLOR]

However, if you simply want the 3rd column, that would mean you want this column in red...

[COLOR=#333333].ColumnWidths = "0;0;[/COLOR][COLOR=#ff0000]150[/COLOR][COLOR=#333333];42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;0;0"[/COLOR]

Can you please confirm which one you actually want?

 

[asyamonique]

2nd visible column "42"
Is it really matter?
I didn't understand.
If I do the

[COLOR=#333333]Me.TextBox6.Value = .CountIf(.Index(b, 0, 4), "S")[/COLOR]

its same ...

 

[Domenic]

Is it really matter?

If we don't need to start searching by code for the nth visible column, it doesn't matter. So, in this case, while you want the 3rd visible column, I'll assume that you simply want the 5th column, which happens to be visible.

.Index(b, 0, 4)

This returns an array, which CountIf will not accept. However, we can loop through b for the desired count. Since b contains transposed data, I'm assuming you want the fifth row, which contains the column from the original data. If so, try...

Dim cnt As Long    

cnt = 0
For i = LBound(b, 2) To UBound(b, 2)
    If UCase(b(5, i)) = "S" Then '1-based index --> 5 = fifth column
        cnt = cnt + 1
    End If
Next i

Me.TextBox6.Value = cnt

Does this help?

 

[asyamonique]

Domenic,
Thanks,
This code gives me the correct result.
Actualy in one column I have 4 type letters to count individually and I think I need to alter this code for each text boxes.
My userform has lots of textboxes for to show visible columns informations which for each letters like below sample

A column shows below letters which entered by the user.So if I have 5 columns I would have 20 textboxes under the listbox.
When the original code runs those textboxes will show me the results individually.

S
W
J
P