在VBA中对变量使用Xlookup会返回#NAME

Question

我正试图在VBA中的Xlookup中使用范围变量，它返回#NAME？

如果我删除变量并硬编码公式中的一个范围，函数就会起作用。我不知道是我没有正确定义我的范围，还是我有一个"；报价问题"；。

这是我的代码：

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

如有任何帮助，我们将不胜感激。

Answer 1

校正：

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