Search Help Please!!!

newbie999 · July 10th, 2006, 04:16 PM

I have lamed together the following code. It is intended to search one spreadsheet from another and return the results into a listbox on another sheet.... I am trying to get the contents from all three columns in the rows found to be returned but I am only getting Column "A" and the "Row Number".... what am I doing wrong?

This should be really simple so I must be just getting to burnt out thinking about this

Code:

Option Explicit

Sub Locate(Name As String, Data As Range)

    Dim rngFind As Range
    Dim strFirstFind As Range
    Dim strRow As Excel.Range
    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.Text
                    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

    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 Sub

Any help or better code would be greatly appreciated...

KALOR55 · July 28th, 2006, 03:43 PM

I changed your code and it works. Please try it:

Dim rngFind As Range
    Dim strFirstFind
    Dim strRow As Excel.Range
    Dim shtSearch As Worksheet
    Dim strSheet As String
    Dim strAddress As String
    Dim Z
Option Explicit

Sub Locate(Name As String, Data As Range)

ListBox1.ColumnCount = 5
    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
                Z = ListBox1.ListCount
                    ListBox1.AddItem Sheets(shtSearch.Name).Cells(rngFind.Row, 1)
                    ListBox1.List(Z, 1) = Sheets(shtSearch.Name).Cells(rngFind.Row, 2)
                    ListBox1.List(Z, 2) = Sheets(shtSearch.Name).Cells(rngFind.Row, 3) '
                    ListBox1.List(Z, 3) = shtSearch.Name
                    ListBox1.List(Z, 4) = rngFind.Address
                End If

                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With

End Sub

Sub CommandButton1_Click()
    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(0, 1) = "No Match Found"
        ListBox1.List(0, 2) = "No Match Found"
        ListBox1.List(0, 3) = "No Match Found"
        ListBox1.List(0, 4) = "No Match Found"
    End If
End Sub

Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ListBox1.TextColumn = 4

If ListBox1.List(ListBox1.ListIndex, 0) <> "No Match Found" Then

    strSheet = ListBox1.List(ListBox1.ListIndex, 3)
    Sheets(strSheet).Select
    Range(ListBox1.List(ListBox1.ListIndex, 4)).Select

End If

End Sub