Index & Match VBA Code

Hey all,

I knew this would be a big undertaking when I took it on, but I have to try and figure this out. I am only on the first part of this which is to pull back the TD name. I figure if I could get that part working first then I could get the rest of it. But I am running into a Type 13, file mistype error in the line that starts strTDName = Application.... and I am not sure why. I think it might have to do with my ranges I have named? Anyway, here is what I have for code, any help would be great. Just spent 10 hours on it today and not moved forward, keep reading sites and not finding what I am looking for.

'overview of process: This is to removed the formula that exisits in the fields to speed up the processing of the supervisors workbook.
'Purpose is to take the tour code and departure from the supervisors workbook and look for TDs name and notes in the schedule workbook.
'that that information and populate it into the supervisors workbook.  This information can be on one of two tabs in the schedule workbook based
'on the region the trip is in.  Also want to note and carry over formatting of the name, whether the name is in bold or not and pull that the supervisors
'workbook.  Also compare the names that exsist on the supervisors workbook to the scheduled workbook, if the name is different then highlight the new
'name on the supervisors workbook to indicate it changed.  Same comparison with the Notes field between the two workbooks.

'Current formula in cell {=IFERROR(INDEX('S:\Operations\Ops\~DA 2012\2012 TOUR DIRECTOR FILES\
'[TD USA & CAD SCHEDULE 2012.xls]USA IV + TT'!$A:$IV,MATCH(B2&C2,'S:\Operations\Ops\~DA 2012\
'2012 TOUR DIRECTOR FILES\[TD USA & CAD SCHEDULE 2012.xls]USA IV + TT'!$A$1:$A$65536&'S:\Operations\Ops\~DA 2012\
'2012 TOUR DIRECTOR FILES\[TD USA & CAD SCHEDULE 2012.xls]USA IV + TT'!$B$1:$B$65536,0),10),(INDEX('S:\Operations\
'Ops\~DA 2012\2012 TOUR DIRECTOR FILES\[TD USA & CAD SCHEDULE 2012.xls]Canada IV + TT'!$A:$IV,MATCH(B2&C2,'S:\Operations\Ops\
'~DA 2012\2012 TOUR DIRECTOR FILES\[TD USA & CAD SCHEDULE 2012.xls]Canada IV + TT'!$A$1:$A$65536&'S:\Operations\Ops\~DA 2012\
'2012 TOUR DIRECTOR FILES\[TD USA & CAD SCHEDULE 2012.xls]Canada IV + TT'!$B$1:$B$65536,0),10)))}

Dim strTDSchedule As String
Dim strCode As String
Dim strDeparture As String
Dim intcounter As Integer
Dim nextRow As Long
Dim strTDName As String
Dim strNote As String
Dim rngTDUSA As range
Dim rngTDCanada As range
Dim rngTDUSACode As range
Dim rngTDUSADeparture As range
Dim TotalRow As Integer
Dim FinalRow As Integer

'want to set up a generic name for file path instead of using the path all the time incase the path changes
Workbooks.Open Filename:="S:\Operations\Ops\~DA 2012\2012 TOUR DIRECTOR FILES\TD USA & CAD SCHEDULE 2012.xls"
strTDSchedule = Workbooks("TD USA & CAD SCHEDULE 2012.xls").Name

TotalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalRow = TotalRow - 1

Set rngTDUSA = Workbooks(strTDSchedule).Worksheets("USA IV + TT").range("$A:$IV")
Set rngTDUSACode = Workbooks(strTDSchedule).Worksheets("USA IV + TT").range("A1:A" & FinalRow)
Set rngTDUSADeparture = Workbooks(strTDSchedule).Worksheets("USA IV + TT").range("B1:B" & FinalRow)

Set rngTDCanada = Workbooks(strTDSchedule).Worksheets("Canada IV + TT").range("$A:$IV")

Workbooks("MattsCalendar2013VBACode.xlsm").Worksheets("Matt's Vacations").Activate
nextRow = Evaluate("Counta(B:B)")

For intcounter = 2 To nextRow

    strCode = range("B" & intcounter).Value
    strDeparture = range("C" & intcounter).Value
    
    strTDName = Application.Index(rngTDUSA, Application.Match(strCode & strDeparture, rngTDUSACode & rngTDUSADeparture, 0), 10)
    'test to see what comes back
    MsgBox ("TD Name is " & strTDName)
    
    strNote = Application.Index(rngTDUSA, Application.Match(strCode & strDeparture, rngTDUSACode & rngTDUSADeparture, 0), 11)
    'test to see what come back
    MsgBox ("Notes is " & strNote)
    
    
Next intcounter
End Sub

Thank you for your help, but that is not working for me either.

Maybe this is a better way of doing this, here is the nutshell version of what I am trying to do.

In Workbook A, I want to take the data in column A & Column B and goto workbook B and match up the data of Column A and Column B and pull back the data in Column C of Workbook B to Workbook A in column C.