How do I Perform an Xlookup within VBA with a wildcard cell range?

Question

For a VBA application I am trying to look for wildcard matches (before or after variable) in another sheet.

Application.XLOOKUP("*" & Mod_SelectColumns.PnPRefRange & "*", Worksheets("Worksheetname").Range("C3:C119"), Worksheets("Worksheetname").Range("B3:B119"), "NOT FOUND", 2). 

Mod_SelectColumns.PnPRefRange is a global Range variable.

For some reason I can't manage to combine the "*" (wildcard symbols) with the variable.

How can I manage that?

I tried using a string range as a place holder, this does work.

Answer 1

Turns out you can loop through your range and perform XLOOKUPS that way. Here's an example sub statement using two tables on two seperate worksheets:

Sub FruitXlookup()

    Dim Lookuptring As String
    Dim i As Integer
    Dim SelectedCell As String
    Dim LookupCell As String
    
        For i = 2 To 9
        SelectedCell = "B" & CStr(i)
        LookupCell = "A" & CStr(i)
        Range(SelectedCell) = Application.XLOOKUP("*" & Range(LookupCell) & "*", Worksheets("LookupSheet").Range("A2:A5"), Worksheets("LookupSheet").Range("B2:B5"), "NOT FOUND", 2)
        Next i

 
End Sub