Find multiple values and return to a list/array

[Snypa]

Hello guys,

I have the following code:

On Error Resume Next
                
strOut = Application.WorksheetFunction.VLookup(strSearch, Worksheets("StoreLookup").Range("A2:B2000"), 2, False)

strSearch is a string the user wants to find (with wildcards) from a shop's name and it returns the shops account number. Like a fuzzy lookup.

This is great and it returns the first store it finds. I want to change this so it returns all the stores it finds and I can ask the user which result it would like to view. Can I change the code so it returns a list/array of values from the table (both column a and b)?

 

[Snypa]

To clarify:

The code I have:

On Error Resume Next
                
strOut = Application.WorksheetFunction.VLookup(strSearch, Worksheets("StoreLookup").Range("A2:B2000"), 2, False)

Takes a string from the user, encapsulated with wildcards (*), and returns the first match from a vlookup. I would like to know how I would continue returning matches that I can store in an array.

Any help would be greatly appreciated.

 

[Peter_SSs]

See if you can use/adapt something like this. If I have understood correctly the store names and account numbers in the array aryResults.

Dim rngFound As Range
Dim lngFirstRow As Long
Dim lngCol As Long
Dim aryResults As Variant

With Sheets("StoreLookup").Range("A2:A2000")
  Set rngFound = .Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If rngFound Is Nothing Then
    MsgBox strSearch & " not found"
  Else
    lngFirstRow = rngFound.Row
    ReDim aryResults(1 To 2, 0 To 0)
    Do
      lngCol = lngCol + 1
      ReDim Preserve aryResults(1 To 2, 1 To lngCol)
      aryResults(1, lngCol) = rngFound.Value
      aryResults(2, lngCol) = rngFound.Offset(, 1).Value
      Set rngFound = .FindNext(After:=rngFound)
    Loop Until rngFound.Row = lngFirstRow
  End If
End With

 

[Snypa]

Thank you @Peter_SSs, that looks good!

Can I ask, how is this storing the results? It looks like its an array of two arrays, (1, x) being column a and (2, x) being column b, is that correct?

And if so, how do I loop through the array? Sorry, I was hoping arrays would be like Python's lists but (where you can use two for loops).

Any help very welcome!

 

[Snypa]

Nevermind, I figured it out! (LBound(aryResults, 2) To UBound(aryResults, 2))!!

Thanks for the help!!

 

[Peter_SSs]

Thanks for the help!!

You are welcome.