+ Reply to Thread
Results 1 to 8 of 8

Enter very long formula into cell

  1. 01-16-2014, 12:40 PM #1
    EmmaOD
    EmmaOD is offline
    Registered User
    Join Date
    12-13-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Enter very long formula into cell

    Hi everyone,

    I'm trying to set up a macro which puts a very long formula into a cell when a button is pressed. The formula in question is 2000 characters long and using

    ActiveCell.FormulaR1C1 =

    Didn't work, presumably because the formula was too long.

    One option is to store the formula somewhere else in the workbook and just have the macro copy and paste it into the required place but I'd like to avoid that if possible.

    So the code needs to select the sheet titled "Output". Select Cell D2 and dump the long formula into there. Then it just has to be autofilled down to cell D1500.

    Sheets("Output").Select
    Range("D2").Select

    ActiveCell.FormulaR1C1 = BIG FORMULA

    Selection.AutoFill Destination:=Range("D2:D1500"), Type:=xlFillDefault
    Is as far as I got. Can anyone help?
    Reply With QuoteRegister To Reply
  2. 01-16-2014, 12:45 PM #2
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Enter very long formula into cell

    Hello...

    "It didn't work" can mean a bunch of things....it wouldn't compile?...you get an error?....the results weren't as expected?....inquiring minds want to know...sample data would help also....thanks...
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....
    Reply With QuoteRegister To Reply
  3. 01-16-2014, 12:47 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enter very long formula into cell

    Hi,

    Before using the proverbial sledgehammer to crack the proverbial nut would you care to share your mega formula with us and tell us what you are trying to achieve with it.

    90% of the time here we can think of more efficient and simpler ways of working - no guarantee of course but that's one hell of a formula.

    What have you got against holding the formula in a cell somewhere and copying/pasting it? It's a preferred method of mine in many cases.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 01-16-2014, 12:48 PM #4
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Enter very long formula into cell

    Typically, a formula that long can be replaced with better approaches. If you can post the formula, perhaps we can find a better alternative.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  5. 01-16-2014, 12:53 PM #5
    EmmaOD
    EmmaOD is offline
    Registered User
    Join Date
    12-13-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Enter very long formula into cell

    The visual basic editor shows the formula (which when pasted goes into two lines) in red.

    When I press the button to activate the macro the error message reads:

    Compile Error: Syntax Error

    A sample of the formula which repeats quite a lot is:

    =IF(ROWS(B$2:B2)>('Calculation sheet'!$D$509),"",IF('Calculation sheet'!GM511=0,"",INDEX('Calculation sheet 2'!$A$511:$GGB$2000,'Calculation sheet'!GM511,IF(C2='Cover Sheet'!$E$8,4,IF(C2='Cover Sheet'!$E$9,2,IF(C2='Cover Sheet'!$E$10,7,IF(C2='Cover Sheet'!$E$11,9,IF(C2='Cover Sheet'!$E$12,11,IF(C2='Cover Sheet'!$E$13,13,IF(C2='Cover Sheet'!$E$14,15,

    Possibly the quotation marks causing issues?
    Reply With QuoteRegister To Reply
  6. 01-16-2014, 12:55 PM #6
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Enter very long formula into cell

    try this...don't use the ActiveCell.FormulaR1C1 =....use the ActiveCell.Formula =......your formula isn't using the R1C1 convention....plus a lot of quotes that need to be handled...
    Reply With QuoteRegister To Reply
  7. 01-16-2014, 01:00 PM #7
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enter very long formula into cell

    Quote Originally Posted by EmmaOD View Post
    The visual basic editor shows the formula (which when pasted goes into two lines) in red.

    When I press the button to activate the macro the error message reads:

    A sample of the formula which repeats quite a lot is:

    =IF(ROWS(B$2:B2)>('Calculation sheet'!$D$509),"",IF('Calculation sheet'!GM511=0,"",INDEX('Calculation sheet 2'!$A$511:$GGB$2000,'Calculation sheet'!GM511,IF(C2='Cover Sheet'!$E$8,4,IF(C2='Cover Sheet'!$E$9,2,IF(C2='Cover Sheet'!$E$10,7,IF(C2='Cover Sheet'!$E$11,9,IF(C2='Cover Sheet'!$E$12,11,IF(C2='Cover Sheet'!$E$13,13,IF(C2='Cover Sheet'!$E$14,15,

    Possibly the quotation marks causing issues?
    Is that an extract from the formula in the workbook or the formula that you've encoded?

    Please upload your workbook along with a narrative description of what the formula is trying to do. 2000+ characters is if I may say just a little over the top. There will be a more efficient way of doing what you want.
    Reply With QuoteRegister To Reply
  8. 01-16-2014, 01:01 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Enter very long formula into cell

    ''''^''''1
    Entia non sunt multiplicanda sine necessitate
    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] long formula in more cells into one cell?
    By miso.dca in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-28-2013, 07:05 AM
  2. Data Validation. Enter any value in a range of Cells as long as total doesnt exceeds 100%
    By excobra in forum Excel General
    Replies: 3
    Last Post: 11-12-2013, 03:51 AM
  3. [SOLVED] Code too long to enter into one cell?
    By Platinum3x in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2013, 03:55 PM
  4. Break long lines of code, Map enter key with OnKey
    By capngene in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2012, 08:22 AM
  5. How to enter a long address to new web query
    By Pivotrend in forum Excel General
    Replies: 4
    Last Post: 09-02-2005, 06:36 AM

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