Resolving 424 'Object Required' on VBA Index/Match formula

[frateg8r]

I created a VBA version of this successful formula:

=INDEX([DATA.xlsm]FIRMS!$K:$K,MATCH(Home!L19,[DATA.xlsm]FIRMS!$A:$A,0),1)

The script that I produced based on that is:

 RecipientStr = Application.WorksheetFunction.Index(Workbooks(Data.xlsm).Sheets("FIRMS").Range("K:K"), Application.WorksheetFunction.Match(ThisWorkbook.Sheets("Home").Range("L19").Value, Workbooks(Data.xlsm).Sheets("FIRMS").Range("A:A"), 0), 1)

However, that produces, as indicated in the title, a Runtime 424 error, "Object Required". This is my first attempt at creating a formula via VBA, and with that error and some of the stuff I've found online looking for an answer, I'm feeling a bit over my head.

Can anyone offer a suggestion as to what I'm overlooking here?

Thanks

 

[AlphaFrog]

You were very close. Put quotes around the workbook name.
Workbooks("Data.xlsm")

Try something like this using a With code block to shorten it a bit.

[color=darkblue]With[/color] Workbooks("Data.xlsm").Sheets("FIRMS")
    RecipientStr = Application.Index(.Range("K:K"), Application.Match(ThisWorkbook.Sheets("Home").Range("L19").Value, .Range("A:A"), 0), 1)
[color=darkblue]End[/color] [color=darkblue]With[/color]

Also in this instance, the INDEX function could be eliminated in VBA.

[color=darkblue]With[/color] Workbooks("Data.xlsm").Sheets("FIRMS")
    RecipientStr = .Range("K" & Application.Match(ThisWorkbook.Sheets("Home").Range("L19").Value, .Range("A:A"), 0))
[color=darkblue]End[/color] [color=darkblue]With[/color]

 

[JLGWhiz]

I created a VBA version of this successful formula:

=INDEX([DATA.xlsm]FIRMS!$K:$K,MATCH(Home!L19,[DATA.xlsm]FIRMS!$A:$A,0),1)

The script that I produced based on that is:

 RecipientStr = Application.WorksheetFunction.Index(Workbooks(Data.xlsm).Sheets("FIRMS").Range("K:K"), Application.WorksheetFunction.Match(ThisWorkbook.Sheets("Home").Range("L19").Value, Workbooks(Data.xlsm).Sheets("FIRMS").Range("A:A"), 0), 1)

However, that produces, as indicated in the title, a Runtime 424 error, "Object Required". This is my first attempt at creating a formula via VBA, and with that error and some of the stuff I've found online looking for an answer, I'm feeling a bit over my head.

Can anyone offer a suggestion as to what I'm overlooking here?

Thanks

You need quotation marks on your "Data.xlsm" file name. Both places.

 

[frateg8r]

Thanks guys - appreciate the feedback. I'm not surprised I missed something relatively obvious.

And AlphaFrog - thanks for the tips on how to improve the code. I'll have to work on that.