excel - Iferror/Vlookup 使用变量

Question

我记录了一个 Excel 函数(Iferror/Vlookup)，我需要对其进行修改以输入变量以使其更具动态性(允许列移动)。下面是我想做的事情的简要概述。第一部分是记录的函数和我要添加的变量。第二部分是我提出的解决方案。我的问题是我需要将该函数放入 excel 中并将其复制到 50,000 多行。所以我的错误处理解决方案在这里不起作用。是否可以使用 iferror/Vlookup 使原始记录的函数动态化。任何帮助表示赞赏。

Dim Lookup1         As Long
Dim LookupOffset    As Long
Dim LRange          As Range


Lookup1 = -99
LookupOffset = 28

Set LRange = Column("CU:CV")

With Worksheets("consolidated")

        .Cells(2, 99).FormulaR1C1 = _
             "=RC[-71]-IFERROR(VLOOKUP(RC[-12],C[-2]:C[-1],2,FALSE),0)"
        .Cells(2, 99).Copy Range(.Cells(2, 99), .Cells(glLastRow, 99))
        Application.CutCopyMode = False
        .Calculate

 End With

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Proposed Solution
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Res As Variant
On Error Resume Next
Err.Clear
Res = Application.WorksheetFunction.VLookup(Lookup1 - LookupOffset, LRange, 2, False)
If Err.Number = 0 Then
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Value found by VLookup. Continue normal execution.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Value NOT found by VLookup. Error handling code here.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
End If

Answer 1

试试这个:

Sub LookUpMod()

    Dim wSht As Worksheet: Set wSht = ThisWorkbook.Sheets("Consolidated")

    With wSht
        On Error Resume Next
        .Cells(2, 99).Formula = "=XCV34-IFERROR(VLOOKUP(XFC34,$I:$J,2,FALSE),0)"
        .Range(Cells(2, 99), Cells(glLastRow, 99)).FillDown
        .Calculate
        On Error GoTo 0
    End With

End Sub

不过，刚刚注意到您没有 glLastRow正确实例化。让我们知道这是否有帮助。

编辑:

根据与 OP 的聊天:

Function LookUpMod(Str As Variant, Rng As Range, OffsetToRight As Long)
    Application.Volatile
    LookUpMod = Rng.Cells.Find(What:=Str).Offset(0, OffsetToRight).Value
End Function

需要一个简单灵活的查找。