Results 1 to 2 of 2

Thread: Formula Works In Worksheet, NOT In Macro

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Works In Worksheet, NOT In Macro

    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.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Formula Works In Worksheet, NOT In Macro

    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)),"""")"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •