VBA查询：变得充满活力

• 如果代码在包含这些工作表的工作簿中，并且您正在对单个工作表执行此操作，那么我看不出为什么您会使用ActiveSheet而不是使用例如指定（引用）工作表。

With ThisWorkbook.Worksheets("Sheet1")

快速修复

Sub XVerweisIP()
    
    Dim slRow As Long, dlRow As Long
    
    With ActiveSheet
        With .Parent.Worksheets("Update")
            slRow = .Cells(.Rows.Count, "AG").End(xlUp).Row
        End With
        dlRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("U3:X" & dlRow)
            ' Default (Any Locale)
            .Formula _
                = "=IFERROR(XLOOKUP($AG3,Update!$AG$3:$AG$" & slRow _
                & ",Update!U$3:U$" & slRow & "),"""")"
            ' German Locale
            '.FormulaLocal _
                = "=WENNFEHLER(XVERWEIS($AG3;Update!$AG$3:$AG$" & slRow _
                & ";Update!U$3:U$" & slRow & ");"""")"
            .Value = .Value
        End With
    End With

End Sub

一项研究（深入挖掘）

Sub XVerweisIP_Study()
    
    Const SRC_SHEET As String = "Update"
    Const SRC_FIRST_CELL As String = "AG3" ' (last row)
    Const SRC_LOOKUP_COLUMN As String = "AG" ' 2. ... here...
    Const SRC_FIRST_RETURN_COLUMN As String = "U" ' 3. ... and return these...
    
    Const DST_FIRST_CELL As String = "A3" ' (last row)
    Const DST_LOOKUP_COLUMN As String = "AG" ' 1. Find a match of this...
    Const DST_RETURN_COLUMNS As String = "U:X" ' 4. ... here.
    
    If ActiveSheet Is Nothing Then Exit Sub
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub
    
    Dim dws As Worksheet: Set dws = ActiveSheet
    
    Dim dflCell As Range, drrg As Range, drCount As Long
    
    With dws.Range(DST_FIRST_CELL)
        drCount = dws.Cells(dws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If drCount < 0 Then Exit Sub ' no data
        Set dflCell = .EntireRow.Columns(DST_LOOKUP_COLUMN)
        Set drrg = .EntireRow.Columns(DST_RETURN_COLUMNS).Resize(drCount)
    End With
    
    Dim sws As Worksheet: Set sws = dws.Parent.Worksheets(SRC_SHEET)
    
    Dim slrg As Range, srrg As Range, srCount As Long
    
    With sws.Range(SRC_FIRST_CELL)
        srCount = sws.Cells(sws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If srCount < 0 Then Exit Sub ' no data
        Set slrg = .EntireRow.Columns(SRC_LOOKUP_COLUMN).Resize(srCount)
        Set srrg = slrg.EntireRow.Columns(SRC_FIRST_RETURN_COLUMN)
    End With
    
    ' Default (Any Locale)
    
    Dim Formula As String:
    Formula = "=IFERROR(XLOOKUP(" & dflCell.Address(0) _
        & ",'" & SRC_SHEET & "'!" & slrg.Address & ",'" & SRC_SHEET & "'!" _
        & srrg.Address(, 0) & "),"""")"
    
    Debug.Print Formula
    
    drrg.Formula = Formula

    ' German Locale

'    Dim FormulaLocal As String:
'    FormulaLocal = "=WENNFEHLER(XVERWEIS(" & dflCell.Address(0) _
'        & ";'" & SRC_SHEET & "'!" & slrg.Address & ";'" & SRC_SHEET & "'!" _
'        & srrg.Address(, 0) & ");"""")"
'
'    Debug.Print FormulaLocal
'
'    drrg.FormulaLocal = FormulaLocal

    drrg.Value = drrg.Value

    MsgBox "Data looked up (verwiesen).", vbInformation

End Sub