Error showing Run-time error '424' for index match vba

Question

I would like to have a index match vba to be executed for one cell (C14) whereby the lookup cell would be C15. Please help advise where did the code went wrong?

Source data would be export workbook sheet1.

Sub Index_Match()
Dim custName As Range 'sourceRange
Dim BRN As Range 'lookupRange
Dim ws As Worksheet 'current sheet

Dim exportWb As Workbook
Dim exportWs As Worksheet

Set ws = Sheet1
Set exportWb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx")
Set exportWs = exportWb.Worksheets("Sheet1")
Set exportWb = ActiveWorkbook
ThisWorkbook.Activate

Set custName = exportWs.Cells(exportWs.Rows.Count, "B").End(xlUp).Row
Set BRN = exportWs.Cells(exportWs.Rows.Count, "E").End(xlUp).Row

ws.Cells(3, 14).Value = Application.WorksheetFunction.Index(custName, 
Application.WorksheetFunction.Match(Cells(3, 15), BRN, 0))

End Sub

Answer 1

A VBA Lookup: INDEX/MATCH in VBA

Sub VBALookup()
    
    ' Source
    Dim swb As Workbook
    Set swb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx")
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
    Dim slRow As Long: sws.Cells(sws.Rows.Count, "E").End(xlUp).Row
    Dim slrg As Range: Set slrg = sws.Range("E2:E" & slRow)
    Dim svrg As Range: Set svrg = sws.Range("B2:B" & slRow)
    
    ' Destination
    Dim dws As Worksheet: Set dws = Sheet1 ' code name in 'ThisWorkbook'
    Dim dlCell As Range: Set dlCell = dws.Range("O3")
    Dim dvCell As Range: Set dvCell = dws.Range("N3")
    Dim dValue As Variant: dValue = dlCell.Value
    
    ' Attempt to find a match.
    Dim MatchFound As Boolean
    If Not IsError(dValue) Then
        If Len(CStr(dValue)) > 0 Then
            Dim sIndex As Variant: sIndex = Application.Match(dValue, slrg, 0)
            If IsNumeric(sIndex) Then MatchFound = True
        End If
    End If
    
    ' Write result.
    If MatchFound Then
        dvCell.Value = svrg.Cells(sIndex).Value
        MsgBox "Match found.", vbInformation
    Else
        dvCell.Value = Empty
        MsgBox "No match found.", vbExclamation
    End If
        
End Sub