+ 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. 06-27-2021, 03:33 AM #1
    Shamis12
    Shamis12 is offline
    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.
    Reply With QuoteRegister To Reply
  2. 06-27-2021, 10:37 AM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    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.
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  3. 06-27-2021, 12:12 PM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,404

    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" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 06-27-2021, 12:13 PM #4
    Vraag en antwoord
    Vraag en antwoord is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. Run time error 1004: Application-defined or object-defined error in long IF VBA formula
    By lgass in forum Excel Programming / VBA / Macros
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1