• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

0
Fordraiders
Asked:
Fordraiders
  • 4
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
How about a working file?
0
 
Rgonzo1971Commented:
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
 
FordraidersAuthor Commented:
Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now