clarkerots
New Member
- Joined
- Mar 29, 2019
- Messages
- 10
I'm using a INDEX/MATCH formula to pull data over from one Excel spreadsheet to another. My goal is that when data ends up as an #NA or #REF , the cell stays blank. I'm using IFERROR for that part. When I enter the formula in the cell, it works fine. When I enter the formula in a macro and run, I get an object error.
The formula in the worksheet is:
=IFERROR(INDEX('[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$AZ$3:$AZ$14,MATCH(A2,'[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$C$3:$C$14,0)),"")
That takes my data in A2, matches it, and displays the data I'm looking to pull. Also, the range on my final worksheet is C2:C22.
In my Macro, called Test, the formula is the following:
Sub Test()
Range("C2:C22").Formula = "=IFERROR(INDEX('[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$AZ$3:$AZ$14,MATCH(A2,'[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$C$3:$C$14,0)),"")"
End Sub
When I run the Macro I get a Run-Time error '1004': Application-defined or object-defined error.
Why would a formula run on a worksheet, and the same exact formula give me an error in a macro?
Any help appreciated. Thank you.
The formula in the worksheet is:
=IFERROR(INDEX('[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$AZ$3:$AZ$14,MATCH(A2,'[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$C$3:$C$14,0)),"")
That takes my data in A2, matches it, and displays the data I'm looking to pull. Also, the range on my final worksheet is C2:C22.
In my Macro, called Test, the formula is the following:
Sub Test()
Range("C2:C22").Formula = "=IFERROR(INDEX('[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$AZ$3:$AZ$14,MATCH(A2,'[Basketball Scoresheet-Gameday.xlsm]Sheet1'!$C$3:$C$14,0)),"")"
End Sub
When I run the Macro I get a Run-Time error '1004': Application-defined or object-defined error.
Why would a formula run on a worksheet, and the same exact formula give me an error in a macro?
Any help appreciated. Thank you.