Formula Works In Worksheet, NOT In Macro

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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,744
Office Version
365
Platform
Windows
You need to double-up on the quotes inside the formula like
Code:
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)),"[COLOR=#ff0000]""[/COLOR]")"
 

Watch MrExcel Video

Forum statistics

Threads
1,095,704
Messages
5,446,030
Members
405,378
Latest member
pvergili

This Week's Hot Topics

Top