Excel VBA实现XLOOKUP动态适配每周变化的表格范围

直接使用以下修改后的代码，即可实现自动适配Update工作表的实际数据范围：

Sub XVerweisIP()
    Dim LR As Long
    Dim UpdateLR As Long
    Dim wsUpdate As Worksheet
    
    ' 定义Update工作表对象，避免ActiveSheet切换出错
    Set wsUpdate = ThisWorkbook.Worksheets("Update")
    
    With ActiveSheet
        ' 获取当前工作表A列最后一行
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    ' 获取Update工作表AG列最后一行（XLOOKUP的查找范围基准）
    UpdateLR = wsUpdate.Cells(wsUpdate.Rows.Count, "AG").End(xlUp).Row
    
    With ActiveSheet
        ' 写入动态范围的XLOOKUP公式
        .Range("U3:U" & LR).FormulaLocal = "=WENNFEHLER(XVERWEIS(AG3;Update!$AG$3:$AG$" & UpdateLR & ";Update!$U$3:$U$" & UpdateLR & ");"""")"
        .Range("V3:V" & LR).FormulaLocal = "=WENNFEHLER(XVERWEIS(AG3;Update!$AG$3:$AG$" & UpdateLR & ";Update!$V$3:$V$" & UpdateLR & ");"""")"
        .Range("W3:W" & LR).FormulaLocal = "=WENNFEHLER(XVERWEIS(AG3;Update!$AG$3:$AG$" & UpdateLR & ";Update!$W$3:$W$" & UpdateLR & ");"""")"
        .Range("X3:X" & LR).FormulaLocal = "=WENNFEHLER(XVERWEIS(AG3;Update!$AG$3:$AG$" & UpdateLR & ";Update!$X$3:$X$" & UpdateLR & ");"""")"
        
        ' 仅将有效行的公式转为值，避免处理空行
        .Range("U3:X" & LR).Value = .Range("U3:X" & LR).Value
    End With
    
    Set wsUpdate = Nothing
End Sub

关键修改点说明： #

• 新增Update工作表的动态行计算：用UpdateLR变量获取Update表AG列的最后一行，替代原代码中固定的30000，确保XLOOKUP范围始终匹配实际数据量。
• 明确工作表对象：定义wsUpdate对象，避免因ActiveSheet切换导致的错误，代码更稳定。
• 动态赋值范围：最后一步将公式转值时，使用当前表的LR变量，仅处理有数据的行，避免修改空行。

额外优化建议： #

• 如果Update表的AG列可能存在空行，可以用从底部向上查找最后非空单元格的方法（当前方法适用于数据连续的场景）。
• 可以把重复的公式部分封装成循环，减少代码冗余：

Dim col As Variant
For Each col In Array("U", "V", "W", "X")
    .Range(col & "3:" & col & LR).FormulaLocal = "=WENNFEHLER(XVERWEIS(AG3;Update!$AG$3:$AG$" & UpdateLR & ";Update!$" & col & "$3:$" & col & "$" & UpdateLR & ");"""")"
Next col

内容的提问来源于stack exchange，提问作者Blackjack