+ Reply to Thread
Results 1 to 6 of 6

Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2007/2010
    Posts
    18

    Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    Good evening all,

    I'm getting a runtime error stating unable to set the formulaarray property of the Range class.

    I need this to enter an index match formula in wb1 AF2 that returns a value from wb2 by matching 4 different criteria. What am I doing wrong?

    Please Login or Register  to view this content.
    Last edited by jobell; 02-10-2023 at 11:57 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,171

    Re: Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    I can't see an obvious problem but that's a tricky formula to read.

    I suggest you declare a variable, put the formula in the variable, then print it before trying to assign it to a cell. Then show us the finished formula as it is printed in the Immediate window.
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-12-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2007/2010
    Posts
    18

    Re: Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    Thanks, 6StringJazzer!

    I did try running this code with the actual file name in place of the variable wb2 in the formula line of code and it does work. I'm sure there is a cleaner way to do a multiple variable index/match but I'm pretty green with VBA.

    I just tried running this and I'm getting the same error on the last line of code.

    Attachment 817108

    Please Login or Register  to view this content.
    Last edited by jobell; 02-09-2023 at 11:52 PM. Reason: spelling

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    Pl check in wb2 sheet "StopTimeDetails" is existing or the name is not exactly matching (Eg: like extra space or missing space). Except that there is no other problem.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    06-12-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2007/2010
    Posts
    18

    Re: Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    Solved the issue. My referenced file name for wb2 was so long that it caused the array formula to be too long as well. I don't know what the max length for an array formula is but by reducing the file name to 15 characters, it was short enough to work.

    Original file name for wb2 - WeekToDate_Stop Time Analysis_021023.xlsx
    Updated file name for wb2 - WTD_021023.xlsx

    Thank you all for your help.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,171

    Re: Formula Array Index Match w/ Variable Filename - Run-time Error '1004'

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)
    By Shamis12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2021, 12:13 PM
  2. Variable for Filename and sheet in INDEX-MATCH formula!
    By elmhed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2021, 04:05 PM
  3. Index and Match Run time error 1004
    By forrestgump1980 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-20-2018, 08:58 AM
  4. Replies: 13
    Last Post: 01-24-2016, 09:27 PM
  5. Index Array Match - 1004 Error
    By Nipius in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-07-2016, 10:54 AM
  6. [SOLVED] Macro button generates "run time error 1004 [FileName] could not be found' msg
    By dawnmau in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-12-2012, 11:55 AM
  7. error 1004 using variable as a filename in opentext
    By leetslla in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2011, 03:16 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1