Using Xlookup in VBA with variables is returning #NAME?

PhilNBlanks 0
Viewed 27

I am trying to use range variables with Xlookup in VBA and it returns #NAME?

If I remove the variables and hard code a range in the formula the function works. I don't know if I'm not define my ranges correctly or I have a "quote issue".

Here is my code:

Sub InsertXLookup()
    Dim LastRow_A As Long
    Dim LastRow_M As Long
    Dim LookupRng As Range
    Dim SearchRng As Range
    
    'Defing the last row oftwo different ranges
    LastRow_A = shFollowup.Range("A1").End(xlDown).Row ' Last row of output range
    LastRow_M = shFollowup.Range("M1").End(xlDown).Row  ' Last row of output range
    
    ' Define Xlookup ranges
    Set LookupRng = Range("C2:C" & LastRow_A)
    Set SearchRng = Range("A2:G" & LastRow_A)

    ' These two lines of code works
     shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2,C2:C200, A2:G200,""PO Added"",)"
     shFollowup.Range("N2:N" & LastRow_M).FillDown
     
     'THESE TWO LINES OF CODE DO NOT WORK.  #NAME? is retruned to Column N
    shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2,LookupRng, SearchRng,""PO Added"",)"
    shFollowup.Range("N2:N" & LastRow_M).FillDown

End Sub

Any help would be appreciated.

macos vba excel
1 Answers

Correction:

Option Explicit
Sub Example()

    Dim LastRow_A As Long
    Dim LastRow_M As Long
    Dim LookupRng As Range
    Dim SearchRng As Range
    Dim shFollowup As Worksheet
    
        ' >>> Whatever your sheet number actually is:
    Set shFollowup = Sheet1
    
    'Defing the last row oftwo different ranges
    LastRow_A = shFollowup.Range("A1").End(xlDown).Row ' Last row of output range
    LastRow_M = shFollowup.Range("M1").End(xlDown).Row  ' Last row of output range
    
    ' Define Xlookup ranges
    Set LookupRng = Range("C2:C" & LastRow_A)
    Set SearchRng = Range("A2:G" & LastRow_A)

    ' These two lines of code works
     shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2,C2:C200, A2:G200,""PO Added"",)"
     shFollowup.Range("N2:N" & LastRow_M).FillDown
     
     'THESE TWO LINES OF CODE DO NOT WORK.  #NAME? is retruned to Column N
    shFollowup.Range("N2").Formula2 = "=XLOOKUP(M2," & LookupRng.Address & ", " & SearchRng.Address & ",""PO Added"",)"
    shFollowup.Range("N2:N" & LastRow_M).FillDown

End Sub
Cameron Critchlow0
Related