Handle IfError on VBA function

Question

I need to create a formula in VBA where it need to have the option of iferror.

Basically my formula in Excel would be:

Iferror(vlookup(A1&A2;A:B;2;FALSE);vlookup(A1;P:Q;2;FALSE))

Then I tried to replicate that into only one formula in VBA

Function DestAcc ( Account as string, FA as string)

Dim rng1,rng2 as range 

With Workbooks(“ACCOUNTS”).worksheets(“Accounts”)
Set rng1=.Range(.cells(1,1),cells(50000,2)
Set rng2=.Range(.cells(1,16),cells(50000,17)

DestAcc=WorksheetFunction.IfError(WorksheetFunction.VLookup(Account & FA, rng1, 2, False), WorksheetFunction.VLookup(Account, rng2, 2, False))

End function

Then in the Workbook I put on an empty cell

=DestAcc(C1;D1)

And I get the result #VALUE

If I try the 2 members individually I get a result, if I try the function with the "IfError" I always get #VALUE.

Can someone help me?

Thank you

Answer 1

A Conditional Consecutive LookUp

• Put all three procedures in a standard module (e.g. Module1) of the workbook containing this code.

• Adjust luAccount, luFA and ws.

  Note: You have to use the extension of the open workbook (.xlsm, .xlsx, .xls)

• This solution ignores case i.e. A=a.

• You already know how to use it in Excel.

The Code

Option Explicit

' A Conditional Consecutive Lookup
Function DestAcc(Account As String, FA As String)

    Application.Volatile

    Dim luAccount As Variant, luFA As Variant
    ' Specify: First Rows, Match Columns, Value Columns
    luAccount = Array(1, 1, 2)
    luFA = Array(1, 16, 17)

    Dim ws As Worksheet
    ' Either on the ActiveSheet:
    'Set ws = Cells.Worksheet ' or Application.ThisCell.Worksheet
    ' or on a specified worksheet:
    On Error GoTo exitProcedure
        Set ws = Workbooks("Accounts.xlsm").Worksheets("Accounts")
    On Error GoTo 0

    Dim rng As Range
    Dim vMatch As Variant, vValue As Variant
    Dim MatchIndex As Long
    Dim Criteria As String

    ' 1st LookUp
    Set rng = getPartialColumn(ws, luAccount(0), luAccount(1))
    If rng Is Nothing Then GoTo SecondLookUp
    vMatch = rng: vValue = rng.Offset(, luAccount(2) - luAccount(1))
    Criteria = Account & FA: GoSub findMatch

    ' 2nd LookUp
SecondLookUp:
    Set rng = getPartialColumn(ws, luFA(0), luFA(1))
    If rng Is Nothing Then GoTo exitProcedure
    vMatch = rng: vValue = rng.Offset(, luFA(2) - luFA(1))
    Criteria = Account: GoSub findMatch

GoTo exitProcedure

findMatch:
    MatchIndex = getMatchIndex(Criteria, vMatch)
    If MatchIndex > 0 Then GoTo returnLookup
Return

returnLookup:
    DestAcc = vValue(MatchIndex, 1)
GoTo exitProcedure

exitProcedure:

End Function

' Returns the column range from a specified row to the last non-empty row.
Function getPartialColumn(WorksheetObject As Worksheet, _
  Optional ByVal FirstRowNumber As Long = 1, _
  Optional ByVal columnNumber As Long = 1) As Range
    Dim rng As Range
    With WorksheetObject
        Set rng = .Columns(columnNumber).Find(What:="*", _
          LookIn:=xlFormulas, SearchDirection:=xlPrevious)
        If rng Is Nothing Then Exit Function
        Set getPartialColumn = .Range(.Cells(FirstRowNumber, columnNumber), rng)
    End With
End Function

' Returns the index of a found value in an array, or 0 if not found.
Function getMatchIndex(MatchValue As Variant, MatchArray As Variant) As Long
    If Not IsError(Application.Match(MatchValue, MatchArray, 0)) Then
        getMatchIndex = Application.Match(MatchValue, MatchArray, 0)
    End If
End Function

Answer 2

Give this a try.

instead of using worksheetfunction use application

UPDATE: Set the reference to the workbook with the extension

Function DestAcc(Account As String, FA As String)

    Dim accountsWorkbook As Workbook
    Dim accountsWorksheet As Worksheet

    Dim accountsWithFARange As Range
    Dim AccountsOnlyRange As Range

    Dim resultAccountFA As Variant
    Dim resultAccount As Variant

    Set accountsWorkbook = Workbooks("Accounts.xlsm")
    Set accountsWorksheet = accountsWorkbook.Worksheets("Accounts")

    With accountsWorksheet
        Set accountsWithFARange = .Range("$A$1:$B$50000") ' $A$1:$B$50000
        Set AccountsOnlyRange = .Range("$P$1:$Q$50000") ' $P$1:$Q$50000
    End With


    resultAccountFA = Application.VLookup(Account & FA, accountsWithFARange, 2, False)
    resultAccount = Application.VLookup(Account, AccountsOnlyRange, 2, False)

    DestAcc = IIf(Not IsError(resultAccountFA), resultAccountFA, resultAccount)

End Function

Note: Your code has a couple of flaws:

• You are defining rng1 as variant (this Dim rng1,rng2 as range is not the same as Dim rng1 as range, rng2 as range)
• You are not closing the With block (missing End With)

Some suggestions: - Always define the variables types (even if you're expecting a variant result)

• Try to name your variables to something anybody can understand (rng1 doesn't mean much)

• Try to write short lines (the iferror mixed with the worksheetfunction.vlookup could be splitted in two)

• The way you are setting the ranges is difficult to read. you can use Set rng1 = .Range($A$1:$B$50000)

let me know if it works.