I have a search function which works as needed apart from one thing, I would like the results displaying in reverse order, currently they are returned from the first row to last.
I know nothing about vba and would guess that I'd need to alter one or more of the worksheet range, the After and possibly the SearchDirection to xlPrevious. Any help would be appreciated!
Private Sub cmdSearch_Click()
'SEARCH
Dim Cnt As Long
Dim Col As Variant
Dim FirstAddx As String
Dim FoundMatch As Range
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim Wks As Worksheet
StartRow = 2
Set Wks = Sheets("Data")
Col = cbxCategory.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a category."
Exit Sub
End If
If tbxCriteria.Text = "" Then
MsgBox "Please enter a search term."
tbxCriteria.SetFocus
Exit Sub
End If
LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
Set Rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))
Set FoundMatch = Rng.Find(What:=tbxCriteria.Text, _
After:=Rng.Cells(1, 1), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FoundMatch Is Nothing Then
FirstAddx = FoundMatch.Address
lsvResults.ListItems.Clear
Do
Cnt = Cnt + 1
R = FoundMatch.Row
lsvResults.ListItems.Add Index:=Cnt, Text:=R
For Col = 1 To 14
Set C = Wks.Cells(R, Col)
lsvResults.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=C.Text
Next Col
Set FoundMatch = Rng.FindNext(FoundMatch)
Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
SearchRecords = Cnt
Else
lsvResults.ListItems.Clear
SearchRecords = 0
MsgBox "No match found for " & tbxCriteria.Text
End If
End Sub
Bookmarks