Type Mismatch on worksheetFunction.Xlookup, how to correct?

Question

I am trying to get the value from dbAccountRng (column D in Transaction Database) from the xlookup function, if three variable match. Heres the code:

Private Sub USBank_Click()

UserForm1.Hide

'Get bank download file

Dim bankDownload As Variant
Dim fileName As String

myFile = Application.GetOpenFilename("Excel Files (*.csv*),*.csv", , "Choose Bank Download File", "Open", False)
If myFile = False Then
Exit Sub
Else
fileName = Dir(myFile)
Workbooks.Open (myFile)
Workbooks(fileName).Worksheets(1).Copy After:=ThisWorkbook.Sheets(1)
ActiveSheet.name = "Bank Download"
Workbooks(fileName).Close
End If

'get info from bank download file

Dim entryPath As String
Dim dataPath As String
Dim bankInfoFile As String
Dim databaseFile As String
Dim yardiTemplate As String
entryPath = Application.ThisWorkbook.Path
dataPath = entryPath & "\Data"
bankInfoFile = dataPath & "\subCDE Bank Info.xlsx"
databaseFile = dataPath & "\Transaction Database.xlsx"
yardiTemplate = dataPath & "\Upload Template.csv"

Dim lastRow As Long
Dim rng As Range
Dim ws As Worksheet
Dim amount As Variant
Dim subCDE As Variant
Dim debit As Boolean
Dim tempDate As Variant
Dim tranDate As Date
Dim account As String
Dim description As String
Dim detail As String
Dim yardiCode As String
Dim bankAcct As String
Dim dbAccount As String
Dim dbExist As Boolean
Dim currentRow As Long

Set ws = ThisWorkbook.Sheets("Bank Download")

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

ThisWorkbook.Sheets("Bank Download").Range("A:A").Insert

Set rng = ws.Range("B2:K" & lastRow)

For i = 1 To rng.Rows.Count
    amount = rng(i, "A").Value
    subCDE = rng(i, "C").Value
    account = rng(i, "D").Value
    debit = rng(i, "E") = "Debit"
    tempDate = rng(i, "F").Value
    currentRow = i + 1
    
    If Len(tempDate) = 7 Then
    tranDate = Mid(rng(i, "F"), 1, 1) & "-" & Mid(rng(i, "F"), 2, 2) & "-" & Mid(rng(i, "F"), 4, 4)
    Else
    tranDate = Mid(rng(i, "F"), 1, 2) & "-" & Mid(rng(i, "F"), 3, 2) & "-" & Mid(rng(i, "F"), 5, 4)
    End If
    
    'ws.Range("G" & currentRow) = tranDate
    
    Workbooks.Open databaseFile
    
    Dim tranDateRng As Range
    Dim subCDERng As Range
    Dim dbAmountRng As Range
    Dim dbAccountRng As Range
        
    Set tranDateRng = Workbooks("Transaction Database.xlsx").Worksheets(1).Range("A:A")
    Set subCDERng = Workbooks("Transaction Database.xlsx").Worksheets(1).Range("B:B")
    Set dbAmountRng = Workbooks("Transaction Database.xlsx").Worksheets(1).Range("C:C")
    Set dbAccountRng = Workbooks("Transaction Database.xlsx").Worksheets(1).Range("D:D")
        
    
    dbAccount = WorksheetFunction.XLookup(1, (tranDateRange = tempDate) * (subCDERange = subCDE) * (dbAmountRng = amount), dbAccountRng, "")
    
    
    'ws.Range("N" & currentRow) = "=XLOOKUP(1,('[Transaction Database.xlsx]Transactions'!$A:$A=G" & currentRow & ")*('[Transaction Database.xlsx]Transactions'!$B:$B=""" & subCDE & """)*('[Transaction Database.xlsx]Transactions'!$C:$C=" & amount & "),'[Transaction Database.xlsx]Transactions'!$D:$D,""False"")"
    'dbExist = ws.Range("N" & currentRow)
    'ws.Range("N" & currentRow).Clear
    
    If InStr(UCase(rng(i, "G")), UCase("Miscellaneous Fee(s)")) > 0 Then
    description = "Bank Fee"
  
    Workbooks("Transaction Database.xlsx").Worksheets(1).Select
    Workbooks("Transaction Database.xlsx").Worksheets(1).Range("A1048576").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell = tranDate
    ActiveCell.Offset(0, 1).Select
    ActiveCell = subCDE
    ActiveCell.Offset(0, 1).Select
    ActiveCell = amount
    ActiveCell.Offset(0, 1).Select
    ActiveCell = description
    Else
    
    
    
    'ws.Range("N" & currentRow) = "=XLOOKUP(1,('[Transaction Database.xlsx]Transactions'!$A:$A=G" & currentRow & ")*('[Transaction Database.xlsx]Transactions'!$B:$B=""" & subCDE & """)*('[Transaction Database.xlsx]Transactions'!$C:$C=" & amount & "),'[Transaction Database.xlsx]Transactions'!$D:$D)
    
    'If Application.WorksheetFunction.XLookup(
    End If
    
    
    
    
    
    
    
    
    If debit = True And InStr(UCase(rng(i, "I")), UCase("Advisors")) > 0 Then
    detail = "Management Fee"
    Else
    detail = "Interest?"
    End If

Next i







End Sub

If the date, name, and the amount match with what is on the bank statement, i want to be able to get what is in column D of the Transaction Database excel file. Otherwise, if the transaction is not in the database, i want it to be empty/0. Im getting a type mismatch right now, once i get that figured out, then ill fix what shows up if xlookup comes up empty.

Answer 1

When mapping worksheet formulas into VBA, you'll find that there are things you can do in a worksheet formula which do not have an equivalent in VBA.

In those cases it's sometimes easier to fall back on using Evaluate to execute a worksheet formula.

For example (based on your use case): how to match on 3 columns of a data table and return the matched row number.

Option Explicit

Sub TestRowMatch()
    'Formula template
    Const FRM As String = "=MATCH(1,(A:A=DATEVALUE(""<date>""))*(B:B=""<bvalue>"")*(C:C=""<cvalue>""),0)"
    Dim f As String, res, ws As Worksheet, dt, bval, cval
    
    Set ws = ThisWorkbook.Worksheets("Data") 'where the lookup table is
    
    'some lookup values...
    dt = DateSerial(2023, 1, 9)
    bval = "Val008"
    cval = "Blah008"
    
    'insert lookup values for the columns A, B, C
    f = ReplaceTokens(FRM, "<date>", dt, "<bvalue>", bval, "<cvalue>", cval)
    
    'Use a specific worksheet for the evaluation context: do not
    '   use the default `Application.Evaluate` version!
    res = ws.Evaluate(f) 'returns matched row value or error value
    Debug.Print res      ' >> 9

End Sub


'Replace tokens in `txt`: pass pairs of token+replacement values to `args()`
'Eg:  s = ReplaceTokens("Please {verb} this {noun}","{verb}","review","{noun}","report")
Function ReplaceTokens(txt As String, ParamArray args()) As String
    Dim i As Long, rv As String
    rv = txt
    For i = LBound(args) To UBound(args) Step 2
        rv = Replace(rv, args(i), args(i + 1))
    Next i
    ReplaceTokens = rv
End Function

Note whether you need quotes around the lookup values in the formula will depend on the type of data in the columns used for each value - eg. if numeric then there's no need for quotes.

My test data table: