Solved: Find in 3 colums and list the result in a list box..

[Macrosian]

I need to find a value in 3 colums of a worksheeet (Matching in any of the colums) and results (3 colums) need to show in a list box (3 colums list box), I will list my code for single column.. Please change it to three

I will list my code..
--------------------------
for my textbox
-------------------------

Private Sub txt_fnd_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Locate txt_fnd.Text, "DC9", DeCo9
End Sub
-----------------------------------
function for this
-----------------------------------------


Function Locate(Name As String, sheet As String, fnam As UserForm)
If Name = "" Then
Name = "*"
End If
Dim rngFind As Range
Dim strFirstFind As String
fnam.ListBox1.Clear
With Worksheets(sheet).Range("a1:a500")
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 0 Then
fnam.ListBox1.AddItem rngFind.Cells.Value
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With
End Function
--------------------
DC9 is the worksheet
DeCo9 is the form name
txt_fnd.Tex is my text box

[mdmackillop]

Welcome to VBAX
Can you post a sample workbook. Use Manage Attachments in the Go Advanced reply section.

[Macrosian]

Kindly check the attachment and please...please convert it into a 3 column list box with its code..

[Macrosian]

Hello,

Is there anybody to find a solution for this...

[Tinbendr]

Try this.

I'm still not sure if this is the way the userform is supposed to operate, so you have have to 'undo' some of my changes.

[Macrosian]

Dear Tin,

Many thanks for your help, But the "Locate" function is actually searching only on the first column, how i can make it work for 3 colums,

If any of the column value match with the entered string, it shoul display the 3 colums.......

[Macrosian]

if i search of "kingdom" from this sheet (Sheet look like this ) and the value in the list box also need to come like this

Kingdom | Super | Mathematics
Mathematics | Kingdom | Super
Mathematics | Super | Kingdom

[Tinbendr]

OK I think I've got it.

First, expand your search range.
With Worksheets(sheet).Range("a1:c500")

Next, wherever you fill the listbox use

With fnam.ListBox1
.AddItem Worksheets(sheet).Cells(rngFind.Row, 1)
.Column(1, .ListCount - 1) = Worksheets(sheet).Cells(rngFind.Row, 2)
.Column(2, .ListCount - 1) = Worksheets(sheet).Cells(rngFind.Row, 3)
End With
End If This uses the Row of rngFind to insert the three columns.

[Macrosian]

Yes,

You got it.... many thanks.... Still i need your help, I will comeback if i find any difficulties when i am incorporating this to my project....