Solved

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

Posted on 2014-04-02
7
250 Views
Last Modified: 2014-04-07
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
Comment
Question by:fordraiders
  • 4
  • 2
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39974130
How about a working file?
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39974201
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39975806
Thanks
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 3

Author Comment

by:fordraiders
ID: 39979736
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39979765
Array1 = Application.Index(ListBox1.List, , 38)

  THIS ARRAY stays empty ?

routine not working properly...sorry...
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39982685
HI,

Have you tried to use

Array1 = Application.Index(UserForm2.ListBox32.List, , 38)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39984851
yes. That's.
Array1 = Application.Index(UserForm2.ListBox32.List, , 38)  
that's what I used.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Basic Excel DataEntry for Dashboard analysis 3 27
Rename multiple files in folder from list in table/excel. 4 41
Excel Calculate Average - Grouped Values 7 25
If help 9 50
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question