Solved

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

Posted on 2014-04-02
7
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 43

Expert Comment

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 51

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
manual process automation 5 48
modify formula for range changes 8 31
EXCEL String-handling question ... 7 51
Embed XL WS and Objects into a PPTX 8 34
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 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