Error 13 - Type mismatch - Index Match

Question

Getting the above error on the Index/Match. Will try and keep this short and sweet but I am a VBA noob. Everything that is called has data in. One thing I noticed was that RefCol (a range of numbers) has leading and trailing whitespace when I do a Debug Print. However when I tested the length of the value it returned the correct values.

I can't understand what is breaking it, I did an index match in the workbook itself and it works perfectly.

Private Sub Ref_Change()

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Worksheets("Details")
Set tbl = ws.ListObjects("Call_Log")

Dim RefCol As Range
Dim NameCol As Range
Dim PhoneCol As Range
Dim DateCol As Range

Set RefCol = tbl.ListColumns("Ref Number").DataBodyRange
Set NameCol = tbl.ListColumns("Caller Name").DataBodyRange
Set PhoneCol = tbl.ListColumns("Telephone").DataBodyRange
Set DateCol = tbl.ListColumns("Date").DataBodyRange

Me.CallDate.Value = Application.WorksheetFunction.Index(DateCol, Application.Match(Me.Ref.Value, RefCol, 0))

End Sub

Have I set this up correctly?

Thanks

Evan

RefCol needs to be a single column. Same with DateCol or you can add a column index numver to Index. — user4039065
Likely the late-bound Match call is returning some #VALUE! error value, and since that type can't be coerced to anything it makes the early-bound Index call raise a type mismatch error. Replace Application.Match with Application.WorksheetFunction.Match to make it raise an error in case of a mismatch, and/or pull it out and compute it separately, and only invoke the Index when you know the Match succeeded. — Mathieu Guindon
CallDate is a text box in a UserForm, is this the problem? The way I want it to work is that you select the reference and then it displays the call data (date, caller, phone number) in boxes on the form - is this impractical/impossible? Thanks — user2073606
@Jeeped - they are single columns in my table, I don't know how to check if this is the problem — user2073606

Scott Craner Scott Craner · Accepted Answer · 2018-03-14T17:48:28

As stated most likely the Match is not being found and an error is being passed to the INDEX.

Pull the MATCH out and test for the error before finding the correct cell in the data.

Private Sub Ref_Change()

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Worksheets("Details")
Set tbl = ws.ListObjects("Call_Log")

Dim RefCol As Range
Dim NameCol As Range
Dim PhoneCol As Range
Dim DateCol As Range

Set RefCol = tbl.ListColumns("Ref Number").DataBodyRange
Set NameCol = tbl.ListColumns("Caller Name").DataBodyRange
Set PhoneCol = tbl.ListColumns("Telephone").DataBodyRange
Set DateCol = tbl.ListColumns("Date").DataBodyRange

Dim mtchRow As Long
mtchRow = 0
On Error Resume Next
    mtchRow = Application.WorksheetFunction.Match(Me.ref.Value, RefCol, 0)
On Error GoTo 0
If mtchRow > 0 Then
    Me.CallDate.Value = DateCol.Cells(mtchRow, 1).Value
Else
    MsgBox "'" & Me.ref.Value & "' not found, or lookup array is more than one column or row"
End If



End Sub

Error 13 - Type mismatch - Index Match

1 Answers