I am searching from a userform, the user types in a textbox and as they type the search updates with each keystroke by updating a list of matches in a listbox (when you click on the listbox you open the related .pdf).
On a sheet with 200 rows and 12 columns of data (around 2400 cells) this works seamlessly, I am now searching on a sheet with 2000 rows and 3 columns and it is very slow (20 to 30 seconds) and causing screen lockups on the first keystroke but tends to be faster in subsequent strokes (I guess due to number of matches returned). I have seen reference to "binary search" and bubblesort as being optimal for this purpose but I cannot get my head around them yet.
If someone could point me in the right direction with respect to whether my expectations are too high (for each keystroke to update seamlessly) or whether there is a way to optimise the code and point me in the right direction that would be much appreciated.
Sub formfindmeVGWD() ' called from VGWDSS.spectrasch_change
Dim findthis, lastcolumn, LastRow, rw, firstaddress, srchme
Dim Asheet As String
Dim FoundInRowNumb As Integer
Dim fnd As Boolean
Application.ScreenUpdating = False
Asheet = VGWDSS.SPECTRATYPE.Value 'identifies the sheet, I have tried to use the .codename here but cannot then get it to reference correctly later.
LastRow = Sheets(Asheet).Range("$A$1").End(xlDown).Row 'identifies last row in selected sheet (assumes all in A are filled sequentially)
lastcolumn = Sheets(Asheet).Range("$A$1").End(xlToRight).Column 'identifies last column in selected sheet (assumes all in row 1 are filled sequentially)
lastcell = Cells(LastRow, lastcolumn).Address 'identifies last cell from above information
srchrnge = "$A$2:" & lastcell 'sets search range from A2 to last identified cell, avoiding header row
findthis = VGWDSS.SPECTRASCH.Text ' this where you type the search query
VGWDSS.VGWDRESULT.Clear ' clears the result list in preparation for filling, causes problems if this is removed.
fnd = False
With Sheets(Asheet).Range(srchrnge).Cells 'loops through the cells in the identified range using the .find function, it updates for each keypress. First keypress is taking a long
' time - upto 25 seconds and often locks up the screen.
Set rw = .Find(What:=findthis, After:=Sheets(Asheet).Range("$A$2"), LookIn:=xlValues, lookat:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rw Is Nothing Then
i = 0
firstaddress = rw.Address
Do
fnd = True
If FoundInRowNumb <> rw.Row Then
With VGWDSS.VGWDRESULT
.ColumnCount = 3
.ColumnWidths = "60;60;120"
.AddItem
.List(i, 0) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 0)
.List(i, 1) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 2)
.List(i, 2) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 1)
i = i + 1
End With
FoundInRowNumb = rw.Row
End If
Set rw = .FindNext(rw)
Loop While Not rw Is Nothing And rw.Address <> firstaddress
End If
If fnd = False Then
End If
End With
Application.ScreenUpdating = True
End SubRegards
Justin