I am trying to search one spreadsheet and return the results of the entrie row into the combobox for on partial queries many answer could be available. The data sheet contains three columns. I would like to have the results of all three columns listed in the combobox. I have attempted many variations however, with the code below, my results in the combobox only reflect column "A"----- any help would be greatly appreciated:
Code
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Data
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 1 Then
ListBox1.AddItem rngFind.Value
ListBox1.AddItem rngFind.Row
ListBox1.List(ListBox1.ListCount - 2, 3) = rngFind.Value & "!" & rngFind.Row
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim shtSearch As Worksheet
ListBox1.Clear
For Each shtSearch In ThisWorkbook.Worksheets
Locate TextBox1.Text, shtSearch.Range("A:C")
Next
If ListBox1.ListCount = 0 Then
ListBox1.AddItem "No Match Found"
ListBox1.List(2, 3) = ""
ListBox1.List(2, 2) = ""
End If
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim strSheet As String
Dim strAddress As String
Dim strRow As String
strRow = Application.Union(rRow, rCell)
strSheet = ListBox1.List(ListBox1.ListIndex, 2)
strAddress = ListBox1.List(ListBox1.ListIndex, 3)
If strAddress <> "" Then
Range(strAddress).Activate
End If
End Sub
Private Sub UserForm_Click()
End SubThanks ---