VBA Index Match function

Question

I am new to vba and totally lost in writing the above mentioned function in vba. Actually, I want to do the same thing I would do with the usual excel formula.

Update: Based on the answer of Scott I have adjusted my code. Now I have a Type mismatch error. The Type of Mname by definition is a string. The values in the lookup Range (B18:B38) are (not exclusive) integer. Should I tell Excel to take them as a string? If yes, how?

Summary: I have a range (D18:D38) where I want to chose a value from based on the row number determined by a match between a string Variable (Mname) and another range (B18:B38). The string Variable is determined by the name of a file in a folder.

My Problem is that I get the error message: 'Unable to get the match property of the worksheetfunction class'

Thanks for your help!

Sub Test()
Application.ScreenUpdating = False
    Dim Mname As String
    Set WSCockpit = ThisWorkbook.ActiveSheet

    Dim strFileName As String
    Dim strFolder As String: strFolder = WSCockpit.Range("D9").Value
    Dim strFileSpec As String: strFileSpec = strFolder & "*.xls*"
    strFileName = Dir(strFileSpec)
    Do While Len(strFileName) > 0

    Dim strFilePath As String: strFilePath = strFolder & strFileName

    Mname = Mid(strFileName, 13, Len(strFileName) - 17)

    Dim rw As Long
    rw = Application.Match(Mname, WSCockpit.Range("B18:B38"), 0)

    Dim VarImp As Boolean
    VarImp = Application.WorksheetFunction.Index(WSCockpit.Range("D18:D38"), rw)

'some other task'

Loop

Application.DisplayAlerts = True
End Sub

Backup: Sorry for the code being messy. I have no clue about the general rules for writing vba. Like mentioned before, my goal is to get the lookup running. The looked up value will be "TRUE" or "FALSE". Afterwards I will use this in order to determine whether the file found in the folder needs to be imported or not. If you have some other suggestions for my coding or for the task I want to perform, I would be glad to hear.

Scott Holtzman Scott Holtzman · Accepted Answer · 2020-03-24T15:48:11

Break it into steps and also ensure the data you expect to find in the lookup is in indeed there. Meaning, check the value of Mname at run-time (via debugging code) and verify you can find it manually.

Lastly, use Application.Match instead.

Dim rw as Long
rw = Application.Match(Mname, WSCockpit.Range("B18:B38"),0)

Dim import as Boolean
import = Application.WorksheetFunction.Index(WSCockpit.Range("D18:D38"),rw)

VBA Index Match function

1 Answers