+ Reply to Thread
Results 1 to 4 of 4

Adding Array Large Formula To Cell Via VBA

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Adding Array Large Formula To Cell Via VBA

    I'm trying to add a Array Formula to a cell via this method found here:Because the formula is over 255 characters long.

    http://www.excelforum.com/excel-prog...y-formula.html

    I'm using this script below to add the formula,

    Please Login or Register  to view this content.
    I getting an 1004 error Application Defined or Object Defined at this line

    Please Login or Register  to view this content.
    Here is the original formula I'm trying to get into the cell.

    Please Login or Register  to view this content.
    Anyone have any idea's how to proceed with this cause I'm unsure what is going on.

    Thank You, Mike
    Last edited by realniceguy5000; 08-29-2011 at 02:46 PM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Adding Array Large Formula To Cell Via VBA

    Why do you use
    PHP Code: 
    ROW('UPC Verification'!$A$2)+
    instead of
    PHP Code: 

    you can define 3 named ranges:

    nr1: 'UPC Verification'!$A$2:$A$99
    nr2: 'UPC Verification'!$B$2:$B$99
    nr3: Order!$A$6:$A$30

    resulting in:

    PHP Code: 
    =IF(A2="","",IF(ISNUMBER(MATCH(A2,[nr1],0)),IF(MIN(IF(ISNUMBER(MATCH([nr2]&[nr1],[nr3]&A2,0)),ROW([nr1])-3)=0,"Not Ordered",INDEX([nr2],MIN(IF(ISNUMBER(MATCH([nr2]&[nr1],[nr3]&A2,0)),ROW([nr1])-3)))))) 
    PS. I didn't check whether the formula is correct or not.
    Last edited by snb; 08-29-2011 at 11:00 AM.



  3. #3
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Adding Array Large Formula To Cell Via VBA

    snb,

    I dont know.

    I got help here with the formula, The formula works it;s just so long is why I am trying to add it in via this method. I'm also trying to use shg method in the other post.

    Just not having any luck with this project.

    thank you, mike

  4. #4
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Adding Array Large Formula To Cell Via VBA

    I used Shg's post below
    http://www.excelforum.com/excel-prog...y-formula.html

    post # 7 to correct this problem:


    Please Login or Register  to view this content.
    Thank You, Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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