-
Solved: Find in 3 colums and list the result in a list box..
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
-------------------------
[VBA]
Private Sub txt_fnd_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Locate txt_fnd.Text, "DC9", DeCo9
End Sub
[/VBA]-----------------------------------
function for this
-----------------------------------------
[VBA]
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
[/VBA]--------------------
DC9 is the worksheet
DeCo9 is the form name
txt_fnd.Tex is my text box
-
Welcome to VBAX
Can you post a sample workbook. Use Manage Attachments in the Go Advanced reply section.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Finf & List
Kindly check the attachment and please...please convert it into a 3 column list box with its code..
-
Hello,
Is there anybody to find a solution for this...
-
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.
-
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.......
-
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
-
OK I think I've got it.
First, expand your search range.
[vba]With Worksheets(sheet).Range("a1:c500")
[/vba]
Next, wherever you fill the listbox use
[vba]
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[/vba] This uses the Row of rngFind to insert the three columns.
-
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....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules