Sleeper: Result a search in a list box function

[gibbo1715]

All

I am using the function below to do a search


Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
On Error Resume Next
Set fnFind = sh.Cells.Find(What:=strFind, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Function

Sub TestfnFind()
Dim SearchFor As Range
Set SearchFor = fnFind(InputBox("What do you want to search for", "Search"))
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
SearchFor.Select
End If
End Sub


What i was wondering was how do i get it to display all the hits in a listbox on a userform

Thanks

Gibbo

[gibbo1715]

Can anyone tell me what my error is in this Please?

It errors out when trying to write to the listbox

Thanks


Set oLB = UserForm2.ListBox1
Set c = Columns(Y).Find(TextBox1.Text, , xlValues, xlPart) 'find word
With Columns(Y)
Set c = .Find(TextBox1.Text, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
oLB.AddItem Sheets(X).Cells(c, 1)
oLB.List(oLB.ListCount - 1, 2) = Sheets(X).Cells(c, 2)
oLB.List(oLB.ListCount - 1, 3) = Sheets(X).Cells(c, 3)
oLB.List(oLB.ListCount - 1, 4) = Sheets(X).Cells(c, 4)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

[gibbo1715]

Ok having messed around loads the following seems to work for me, any ideas on improvements most welcome

Gibbo


i = Sheets(X).Range("A63556").End(xlUp).Row 'Ensure the loop only runs for a certain amount of times
Set oLB = UserForm2.ListBox1
For ii = 2 To i
If InStr(Sheets(X).Cells(ii, Y), TextBox1.Text) Then
oLB.AddItem Sheets(X).Cells(ii, 1)
oLB.List(oLB.ListCount - 1, 2) = Sheets(X).Cells(ii, 2)
oLB.List(oLB.ListCount - 1, 3) = Sheets(X).Cells(ii, 3)
oLB.List(oLB.ListCount - 1, 4) = Sheets(X).Cells(ii, 2)
End If
Next ii

[Killian]

Hi Gibbo,

If you're working with a large range (like all the cells on a worksheet), the Find method is going to be the quickest method, the next best would be a For.. Each on a collection, and an incremental loop (For i = 1 to whatever) about 2.5 to 3 time slower.
So I think you were on the right track to start with.
Here's some code that's best added to a button on a userform (tho I've lazily kept the input box - you should just add a textbox to the userform for that, I suppose), using Find and FindNext to do the business


Dim SearchFor As String
Dim firstAddress As String
Dim rngFound As Range
SearchFor = InputBox("What do you want to search for", "Search")
With ActiveSheet.Cells
Set rngFound = .Find(SearchFor, LookIn:=xlValues)
If Not rngFound Is Nothing Then
firstAddress = rngFound.Address
Do
ListBox1.AddItem rngFound.Address
Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And rngFound.Address <> firstAddress
End If
End With

[royUK]

Check these free examples (http://www.excel-it.com/workbookdownloads/workbookdownloads.htm). There is a Datbase form example with a built in Find that counts the instances of the search & lisys all results in a KistBox. The code might help you