Problem with converting Xlookup function to WorksheetFunction.XLookup

[WednesdayC]

Hi Team

Sorry to post twice in one day - I am having a hard time with a workbook!

The following formula works fine in my VBA code for output to the cell:-

wsOutput.Cells(r, 3).FormulaR1C1 = _
"=XLOOKUP(" & strID & ", " & strCell & "!RC10:R1000C10," & strCell & "!RC12:R1000C12)"

Where StrCell is the name of a different worksheet - say "TEMP_1" for now.

I cannot workout how to convert this function so that the output is to a VBA variable instead, which I need.

Please can some help me.

Thank you in advance

Wednesday

 

[Domenic]

I'm on a Windows version of Excel, not a Mac version. So see if this works for you . . .

    Dim myVar As Variant
    myVar = Application.XLookup(strID, Worksheets(strCell).Range("J2:J1000"), Worksheets(strCell).Range("L2:L1000"))
  
    If Not IsError(myVar) Then
        MsgBox "XLOOKUP returned " & myVar
    Else
        MsgBox "Match not found!", vbExclamation
    End If

Hope this helps!

 

[WednesdayC]

I'm on a Windows version of Excel, not a Mac version. So see if this works for you . . .

    Dim myVar As Variant
    myVar = Application.XLookup(strID, Worksheets(strCell).Range("J2:J1000"), Worksheets(strCell).Range("L2:L1000"))
 
    If Not IsError(myVar) Then
        MsgBox "XLOOKUP returned " & myVar
    Else
        MsgBox "Match not found!", vbExclamation
    End If

Hope this helps!

Hi Domenic

Fantastic. It works! Thank you so much. I tried so many combinations, but obviously not the right one!

Best regards

Wednesday

 

[Domenic]

That's great to hear, glad I could help.

Cheers!