在两个不同的工作表中使用XLOOKUP并具有可变数组范围(VBA编码)

Question

大家好，我对VBA编码和编码是个新手，所以我希望你能快速回答我的问题。

我正在尝试将XLookup-Formula添加到我的vba代码中。该代码引用了另一个工作表("Chart Plan“)，并假定将列"D”和"E“(从第2行开始)中的值作为固定数组向下传递到"Lookup array”和"return array“的最后一行。我希望这是可变的，因为“图表计划”是根据我正在做的工作用不同的行号更新的。然后，公式应将值返回到活动工作表(列"J")中，并遍历所有行("B“给定为RC-8 = Lookup value)。问题是，我想，我真的不知道如何在公式中给出数组的语法，或者它是其他的东西吗？RC-Annotation和A1-Annotation之间的混合？

谢谢。

Dim aEndKP As Variant
Dim aStartKP As Variant
Dim aCN As Variant

Sub ChartPlanScript()

Dim row As Long
Dim last_row As Long
Dim rng As Range
Dim ws As Worksheet

'Array End KP

LReKP = Sheets("Chart Plan").Cells(Rows.Count, "D").End(xlUp).row
aEndKP = Sheets("Chart Plan").Range("D2:D" & LReKP)

'Array Start KP

LRsKP = Sheets("Chart Plan").Cells(Rows.Count, "E").End(xlUp).row
aStartKP = Sheets("Chart Plan").Range("C2:C" & LRsKP)

'Array Chart Plan
 
LRCN = Sheets("Chart Plan").Cells(Rows.Count, "E").End(xlUp).row
aCN = Sheets("Chart Plan").Range("E2:E" & LRCN)
 
 
Set ws = Sheets(1)
ws.Activate

last_row = ws.Range("A5000").End(xlUp).row

For row = 2 To last_row
        If Range("A" & row).Value > 0 Then

         ws.Range("J" & row).Value = "=XLOOKUP(RC[-8],[aEndKP],[aCN],,1,1)"

        Else
       ws.Range("J" & row).Value = ""
    End If
Next row

End Sub

Answer 1

我没有在我的Excel版本上安装XLOOKUP，但这将使用VLOOKUP

Option Explicit

Sub ChartPlanScript()

    Dim ws As Worksheet
    Dim i As Long, last_row As Long
    Dim sCN As String

    'Chart Plan
    With Sheets("Chart Plan")
        last_row = .Cells(Rows.Count, "D").End(xlUp).row
        sCN = "'Chart Plan'!" & .Range("D2:E" & last_row).Address
    End With
     
    Set ws = Sheets(1)
    ws.Activate
    last_row = ws.Range("A5000").End(xlUp).row

    For i = 2 To last_row
        If ws.Cells(i, "A") > 0 Then
            ws.Range("J" & i).Formula = "=VLOOKUP(B" & i & "," & sCN & ",2,0)"
        Else
            ws.Cells(i, "J") = ""
        End If
    Next
    MsgBox i - 1 & " rows processed"

End Sub