+ Reply to Thread
Results 1 to 4 of 4

Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)

  1. #1
    Registered User
    Join Date
    06-27-2021
    Location
    Pakistan
    MS-Off Ver
    MS Office 2016
    Posts
    1

    Unhappy Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)

    Hello Everyone,

    I am trying to automate INDEX Array formula (Ctrl+Shift+Enter) using macro.
    but when I run the command, run time error 1004 appears and the code is highlighted with yellow color. This code has 500+ characters. please help me how to split the code in VBA.

    I am new to VBA, please help write the code (of less than 255 character)

    Index formula between sheet1 and sheet3 is mentioned below.

    ={INDEX(Sheet1!$X$2:$X$6500,MATCH(1,(Sheet3!A3=Sheet1!$A$2:$A$6500)*(Sheet3!B3=Sheet1!$B$2:$B$6500)*(Sheet3!C3=Sheet1!$C$2:$C$6500)*(Sheet3!D3=Sheet1!$D$2:$D$6500)*(Sheet3!E3=Sheet1!$E$2:$E$6500)*(Sheet3!F3=Sheet1!$F$2:$F$6500)*(Sheet3!G3=Sheet1!$G$2:$G$6500)*(Sheet3!H3=Sheet1!$H$2:$H$6500)*(Sheet3!AJ3=Sheet1!$AJ$2:$AJ$6500)*(Sheet3!AO3=Sheet1!$AO$2:$AO$6500)*(Sheet3!AP3=Sheet1!$AP$2:$AP$6500)*(Sheet3!AQ3=Sheet1!$AQ$2:$AQ$6500),0))}

    Below is the program that appeared in VBA

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,264

    Re: Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)

    Please read the yellow banner at the top of this page on how to attach a sample workbook.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,803

    Re: Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I'll do it for you today: https://www.mrexcel.com/board/thread...rmula.1174872/.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Run Time Error 1004- Long Array Formula in VBA (How to split the VBA code)

    Although it's pretty useless to use array formulas if you want to solve it with VBA anyway. You can take a look at named ranges. With this you can make the formula a lot shorter and it may fit within the max of 255.

    @AliGW,
    I didn't see your message until after I posted it. 1 minute difference. Can happen and can be removed if I have broken a forum rule with this.
    Last edited by Vraag en antwoord; 06-27-2021 at 12:16 PM.
    Messages have been translated from Dutch to English by means of google translate.

+ 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. [SOLVED] Formula too long? Run-time error '1004'
    By Michela in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2017, 06:53 AM
  2. Error 1004 when pasting a long formula in Macro
    By pauldst in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-14-2017, 04:15 PM
  3. Long Formula Getting 1004 error when running VBA
    By johnmahon12 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2016, 08:08 AM
  4. [SOLVED] Run Time Error 1004 within Array
    By Kramxel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2015, 04:11 AM
  5. Replies: 1
    Last Post: 03-12-2014, 12:42 PM
  6. [SOLVED] How to split this long string code in multiple line to avoid an error???
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2013, 10:08 PM
  7. Code too long? Error 1004
    By mole2704 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2008, 07:25 AM

Tags for this Thread

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