VBA search function

[Milksnatcher]

Hi, I am a VBA beginner doing a project for work and am way out of my depth!!! I have a script which searches all sheets within the workbook for the partial names contained in cells b9-b19 of the "Results" spreadsheet. The findings are then displayed in a listbox in a userform which pops up. The entire cell contents of the match is shown, along with the worksheet name. The script is as follows:

Public Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String

With Data
Set rngFind = .Find(Name, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do

If rngFind.Row > 1 Then
UserForm3.ListBox1.AddItem rngFind.Value
UserForm3.ListBox1.List(UserForm3.ListBox1.ListCount - 1, 1) = Data.Parent.Name
UserForm3.ListBox1.List(UserForm3.ListBox1.ListCount - 1, 2) = Data.Parent.Name & "!" & rngFind.Address
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

End Sub


Sub Searches()


Dim shtSearch As Worksheet
Dim lookout As String

lookout = b9

For Each shtSearch In ThisWorkbook.Worksheets
If Worksheets("Results").Range("b9") <> 0 Then Locate Worksheets("Results").Range("b9").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b10") <> 0 Then Locate Worksheets("Results").Range("b10").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b11") <> 0 Then Locate Worksheets("Results").Range("b11").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b12") <> 0 Then Locate Worksheets("Results").Range("b12").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b13") <> 0 Then Locate Worksheets("Results").Range("b13").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b14") <> 0 Then Locate Worksheets("Results").Range("b14").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b15") <> 0 Then Locate Worksheets("Results").Range("b15").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b16") <> 0 Then Locate Worksheets("Results").Range("b16").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b17") <> 0 Then Locate Worksheets("Results").Range("b17").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b18") <> 0 Then Locate Worksheets("Results").Range("b18").Text, shtSearch.Range("b2:C242")
If Worksheets("Results").Range("b19") <> 0 Then Locate Worksheets("Results").Range("b19").Text, shtSearch.Range("b2:C242")
Next
If UserForm3.ListBox1.ListCount = 0 Then
UserForm3.ListBox1.AddItem "No Match Found"
UserForm3.ListBox1.List(0, 1) = ""
UserForm3.ListBox1.List(0, 2) = ""
End If
UserForm3.Show
End Sub


This works well but there are three things that I can't work out! please help:

1) I want the entire cell contents aligned to the left of the listbox and the worksheet name aligned to the right. The box is wide enough but the worksheet name is overlapping some of the cell contents which needs to be visible. Is this possible?

2) When a match is found there is often a duplicate in the same sheet in a different column. Can I display only one occurence of the cell contents if they are identical?

3) This search is looking through all the sheets and therefore is returning matches from the "Results" worksheet (the search criteria itself). I do not want it to search this one sheet!!!


Help!!!!!