Solved

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

Posted on 2013-11-18
2
302 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 48

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now