Macrosian
06-08-2010, 11:55 PM
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
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