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

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

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
Fordraiders
Asked:
Fordraiders
1 Solution
 
Rgonzo1971Commented:
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
 
FordraidersAuthor Commented:
perfect...Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

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