CODEWITHSUNDEEP
excelvba
Luke K
Luke K

Reputation: 3

Converting Multiple Criteria Xlookup to VBA

I am trying to convert this Xlookup function to VBA to improve the speed of my workbook. I am trying to get VBA to insert the product of this formula into every cell from B2 to the last row:

=(XLOOKUP(1,(J5<=Sheet2!$H:$H)*(J5>=Sheet2!$G:$G)*(F5=Sheet2!$E:$E),Sheet2!$L:$L))

Does anyone know how this can be applied with Application.WorksheetFunction.Xlookup?

Any help would be appreciated. Thanks!

I have tried the below but this does not work:

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set ReturnRange = ws.Range("C:C")
Set LR = ws.Range("J:J")
Set LR2 = ws.Range("F:F")
Set ws2 = wb.Sheets("Sheet2")
Set R = ws2.Range("G:G")
Set R2 = ws2.Range("E:E")
Set R3 = ws2.Range("L:L")
Set R4 = ws2.Range("H:H")

last = ws.Cells(Rows.Count, "K").End(xlUp).Row


result = Application.WorksheetFunction.XLookup(1, (LR1 <= R4) * (LR1 >= R) * (LR2 = R2), R3, "N/A")

ReturnRange.Value = result

End Sub

Upvotes: 0

Views: 353

Answers (1)

taller
taller

Reputation: 18762

The syntax of XLookup could be as below

Set wb = ThisWorkbook
Set ws2 = wb.Sheets("Sheet2")
Set R3 = ws2.Range("L:L")
result = Application.WorksheetFunction.XLookup(1, [(J5<=Sheet2!$H:$H)*(J5>=Sheet2!$G:$G)*(F5=Sheet2!$E:$E)], R3, "N/A")

btw, you can get the same result without using WorksheetFunction.XLookup.

Upvotes: 1

Related Questions