user1624926
user1624926

Reputation: 451

Iferror/Vlookup using variables

I have recorded an Excel function (Iferror/Vlookup) which I need to modify to inpput variables to make it more dynamic (Allow for columns moving). Below is a brief outline of what I want to do. The First section is the recorded Function and the variables I want to add. The second section is my proposed solution. My problem is I need to drop the function into excel and copy it down over 50,000 rows. So my error handling solution won't work here. Is it possilbe to make the original recorded function dynamic using iferror/Vlookup. Any help appreciated.

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

Upvotes: 2

Views: 464

Answers (1)

WGS
WGS

Reputation: 14169

Try this one:

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

Just noticed, though, that you don't have glLastRow instantiated properly. Let us know if this helps.

EDIT:

As per chat with 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

A simple flexible lookup is what is needed.

Upvotes: 1

Related Questions