get count of items in a listbox from another listbox on userform

userform
excel 2010 vba
2 listboxes
Listbox1
Listbox2

First:
I'm populating  listbox1  first from Listbox2(specifically from column 38)

So listbox1 may contain :
Pumps
Motors
Hammers

What I need:
I need to add a count , the the items in listbox1 from Listbox2(column 38)

Example: I had 17 items in Listbox2.  

Listbox1 would now look like.
Pumps (8)
Motors (5)
Hammers (4)

Thanks
fordraiders

' populating listbox1 from Listbox2
ri = 0
UserForm2.ListBox1.Clear
With UserForm2.ListBox2
    For ri = .ListCount - 1 To 0 Step -1
        UserForm2.ListBox1.AddItem .List(ri, 38)
    Next
End With

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try ( With reference to Microsoft Scripting Runtime)

Dim Array1  As Variant
Dim Dict As Dictionary

Array1 = Application.Index(ListBox1.List, , 38)
Set Dict = New Dictionary
For Idx = LBound(Array1, 1) To UBound(Array1, 1)
    If Dict.Exists(Array1(Idx, 1)) Then
        Dict.Item(Array1(Idx, 1)) = Dict.Item(Array1(Idx, 1)) + 1
    Else
        Dict.Add Array1(Idx, 1), 1
    End If
Next
For Each strKey In Dict.Keys()
    Me.ListBox2.AddItem (strKey & " (" & Dict(strKey) & ")")
Next

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerCommented:
How about a working file?
0
 
FordraidersAuthor Commented:
Thanks
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
FordraidersAuthor Commented:
would this work also ?
Dim kk As Variant
kk = UserForm2.ListBox32.List

For ri = 0 To UserForm2.ListBox49.ListCount - 1
 
   With UserForm2.ListBox32
    For jj = .ListCount - 1 To 0 Step -1
       For Each kk In UserForm2.ListBox32
        If UserForm2.ListBox32.List(jj, 38) = UserForm2.ListBox49.List(ri, 0) Then
        UserForm2.ListBox49.List(ri, 0) = UserForm2.ListBox49.List(ri, 0)
        cc = cc + 1
        End If
        Next kk
    Next jj
    UserForm2.ListBox49.List(ri, 1) = "(" & cc & ")"  
    End With
    cc = 0
Next ri
0
 
FordraidersAuthor Commented:
Array1 = Application.Index(ListBox1.List, , 38)

  THIS ARRAY stays empty ?

routine not working properly...sorry...
0
 
Rgonzo1971Commented:
HI,

Have you tried to use

Array1 = Application.Index(UserForm2.ListBox32.List, , 38)
0
 
FordraidersAuthor Commented:
yes. That's.
Array1 = Application.Index(UserForm2.ListBox32.List, , 38)  
that's what I used.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.