Solved

check  3 columns  in a listbox and get a count of occurences for each column

Posted on 2013-11-18
2
328 Views
Last Modified: 2013-11-18
excel 2010 vba

What I have :
Listbox that is filled via an array.
listbox has 23 columns
A variable   "str"

I need to use the variable/value  from a combobox

(The code below is finding the "Like" value in Column 5 if it finds it.
It removes it from the item from the listbox...
I'am showing this as reference.)


What I need:

I need to get a count of   "Like" occurences in Column 5, 13, 19 in the listbox

and return for all three columns the count
cn1 =  2
cn2  = 5
cn3 =  3



If UserForm2.ComboBox1.Value <> "" Then
str = UserForm2.ComboBox1.Value
With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
If Not (LCase(.List(ri, 5)) Like "*" & LCase(str) & "*") Then 'Or Not (LCase(.List(ri, 19)) Like "*" & LCase(str) & "*") Then
             .RemoveItem ri
        End If
    Next
End With
End If


Thanks
fordraiders
0
Comment
Question by:fordraiders
2 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39658534
Hi,

pls try

Sub Macro()
cn1 = 0
cn2 = 0
cn3 = 0

If UserForm2.ComboBox1.Value <> "" Then
Str = UserForm2.ComboBox1.Value
With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
        If LCase(.List(ri, 5)) Like "*" & LCase(Str) & "*" Then
             cn1 = cn1 + 1
        End If
        If LCase(.List(ri, 13)) Like "*" & LCase(Str) & "*" Then
             cn2 = cn2 + 1
        End If
        If LCase(.List(ri, 19)) Like "*" & LCase(Str) & "*" Then
             cn3 = cn3 + 1
        End If
    Next
End With
End If

MsgBox "cn1: " & cn1 & ", cn2: " & cn2 & ", cn3: " & cn3

End Sub

Open in new window

Regards
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39658548
perfect...Thanks
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

821 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