+ Reply to Thread
Results 1 to 13 of 13

With VBA Enter Ctrl Shift Enter For Array Formula

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

    With VBA Enter Ctrl Shift Enter For Array Formula

    Hi,

    Would someone help me?
    I need to enter this formula below in a group of cells with a script Im using, However I need to enter Ctrl Shift Enter to make it work correctly.

    Please Login or Register  to view this content.
    Thank You, Mike
    Last edited by realniceguy5000; 08-25-2011 at 04:19 PM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    hi, Mike, instead of FormulaR1C1 try FormulaArray

    VB help:

    FormulaArray Property

    Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns Null. Read/write Variant.

    Remarks
    If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).

    Example
    This example enters the number 3 as an array constant in cells A1:C5 on Sheet1.

    Please Login or Register  to view this content.
    This example enters the array formula =SUM(R1C1:R3C3) in cells E1:E3 on Sheet1.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    Note that the information in the helpfile is incorrect in that A1 notation is acceptable.
    Hope that helps,

    Colin

    RAD Excel Blog

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

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    Thank You,
    Is this what you mean?

    I tried this:

    Please Login or Register  to view this content.
    I also tried this:
    Please Login or Register  to view this content.
    .

    However I get an error:"Unable to set the FormulaArray property of the range class"

    When I enter this manually into the cell and hit ctrl shift enter it works, I also tried to record a macro but get an error unable to record.

    Any Idea's? Mike
    Last edited by realniceguy5000; 08-25-2011 at 02:44 PM.

  5. #5
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    It's because the maximum length of the formula string is 255 characters. Also, quotes within the string need to be doubled up.

    You can work around the limitation by taking a common part of the formula, substituting in a short string, putting it into the cell and then replacing common part:
    Please Login or Register  to view this content.
    For a comprehensive overview of Range.FormulaArray, see here.

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

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    I think I found why it wont work,

    Ok Looks like my formula is longer than 255 or 256 Characters. I found this information here
    http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

    I'm going to try and get this to work with my formula and I'll post back the results I get. However if someone clever can attempt it as well it would be great as i'm sure I'll have problems. lol

    Thank You, Mike

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    Here's a function I use for inserting large array formulas:

    Please Login or Register  to view this content.
    For example,

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

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

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    Ok, Late as always.

    Colin, I tried your script as follows. It places the formula in the cell with the "xxx" However the replace command wont work. So it wont replace the xxx with the rest of the formula.

    Please Login or Register  to view this content.
    I must have missed something along the way?

    Can you advise: Thank You, Mike

  9. #9
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    Ah, I expect that's because in the interface you have it set to A1, not R1C1 notation. Change the replace string to the A1 equivalent:
    Please Login or Register  to view this content.

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

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    It now works as expected.

    That is something I would not have thought of for some time.

    Anyway, Thanks to all for the input...

    Mike

  11. #11
    Registered User
    Join Date
    09-20-2012
    Location
    North Dakota
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    I know this is an old post, but I hate to create yet another thread for this topic if you can stlll help me out. My formulas were working great in this macro loaded worbook I have but all of a sudden now that the linked tables are refreshed, occasionally I end up with #VALUE error. As such, I've now been looking for a way to "remind" it that it's an array formula without stopping the code, manually hitting ctrl+shift+enter, then continuing the code.

    Now, I played around with this until it worked, but with one exception. One of the cells in the range ends up with an apostrophe preceding the formula and I cannot figure out why. The formulas below are an example of what the code is working with. Red is the one failing, the other is the same formula with the exception of the table column it's looking at - and it works. It's not that it's a bad formula either. Once the apostrophe is removed it works again.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Please Login or Register  to view this content.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    ugogirl,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    09-20-2012
    Location
    North Dakota
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: With VBA Enter Ctrl Shift Enter For Array Formula

    So I'm supposed to have others figure out why code written by someone else sometimes works and sometimes doesn't?

    Ok then. I didn't realize it was "hijacking" a thread to ask this. Not worth posting a new thread to me at this time since I tried something else instead.

+ 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