Advanced search using VBA Excel

Question

I have a list of data (20,000+ products) there is different pricing depending on a condition of the product, users need the ability to look up product pricing at certain grades quickly, my current challenge is using vlookup's fail as often the users will just keyword search and not have the exact description. I want to leave in the existing lookup using vlookup as that will remain a quick search when the user has the exact product title but want to add an advanced search in when they only have a keyword (I have tried data validation searchable lists but they are slow and unreliable)

I have created a Listbox but can't get the code to search my data set and then display the results,

this is the code I'm using (Stock data has all my products and pricing) and my 2nd sheet is called Search where I want users to find what they are looking for

enter code here

Private Sub cmdsearch_click()

Dim Rownum As Long
Dim Searchrow As Long

Rownum = 2
Searchrow = 2

Worksheets("Stock Data").Activate

Do Until Cells(Rownum, 1).Value = ""
If InStr(1, Cells(Rownum, 1).Value, txtkeywords.Value, vbTextCompare) > 0 Then
   Worksheets("Sheet1").Cells(Searchrow, 1).Value = Cells(rownnum, 1).Value
   Worksheets("Sheet1").Cells(Searchrow, 2).Value = Cells(rownnum, 2).Value
   Worksheets("Sheet1").Cells(Searchrow, 3).Value = Cells(rownnum, 3).Value
   Searchrow = Searchrow + 1
End If
Rownum = Rownum + 1



Loop

If Searchrow = 2 Then
MsgBox "Sorry No products found, please request a price"
End If

Lstsearchresults.RowSource = "SearchResults"

End Sub

Thanks but the link is in German and I am more than a little rusty at VBA — Mazimoo, Oct 25 '19 at 12:15
Oh, and with regard to `Range.Find`, Microsoft does have online documentation in multiple languages. — Ron Rosenfeld, Oct 25 '19 at 12:17
@ronrosenfeld I am struggling to understand how I would wrap the Range.find into my code thats why I looked at search — Mazimoo, Oct 25 '19 at 12:24
Another possibility is to [read in your list to a memory based array](https://stackoverflow.com/a/58304857/11936678) for performance. It can speed cell loops costing seconds to near instant. — Plutian, Oct 25 '19 at 12:30
Sorry for the wrong link, here is the correct one: [Range.Find - Microsoft Docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find). It also contains a simple example to illustrate how to loop through all results with the `.Find` method. — riskypenguin, Oct 25 '19 at 12:36

score 0 · Answer 1 · answered Oct 25 '19 at 12:48

As described in the comments, you could use Range.Find like this:

Private Sub cmdsearch_click()

Dim rowResult As Long
Dim strSearch As String

rowResult = 2
strSearch = txtkeywords.Value

Dim rowLast As Long
rowLast = Worksheets("Stock Data").Cells(Rows.Count, 1).End(xlUp).Row

Dim rngFound As Range
Dim firstAdress As String

With Worksheets("Stock Data").Range("A1:A" & rowLast)
    Set rngFound = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
    If Not rngFound Is Nothing Then

        firstAddress = rngFound.Address
        Do
            Worksheets("Sheet1").Cells(rowResult , 1).Value2 = rngFound.Value2
            Worksheets("Sheet1").Cells(rowResult , 2).Value2 = rngFound.Offset(0, 1).Value2
            Worksheets("Sheet1").Cells(rowResult , 3).Value2 = rngFound.Offset(0, 2).Value2
            rowResult = rowResult + 1

            Set rngFound = .FindNext
        Loop While rngFound.Address <> firstAdress

    Else
        MsgBox "Sorry No products found, please request a price"
    End If
End With

Lstsearchresults.RowSource = "SearchResults"

End Sub

An even faster approach would be to store everything in an array and looping through that. When starting out however I found it easier to work with less abstract methods and learning step by step.

Advanced search using VBA Excel

1 Answers1