+ Reply to Thread
Results 1 to 3 of 3

Problem with recording a macro with long formula - works in Excel but not in macro

  1. #1
    Registered User
    Join Date
    10-26-2015
    Location
    Lafayette, LA
    MS-Off Ver
    2013
    Posts
    3

    Problem with recording a macro with long formula - works in Excel but not in macro

    I have a long formula in an Excel sheet and when I record a macro that adds the formula to a cell, it jumps to the next line after a certain number of characters and adds " &_ at the end of the line but 4 or 5 of the characters that were there in excel are now not there in VBA code after the macro has recorded. I added the missing characters and the formula compiles correctly in VBA but when I try to run it on the data in the spreadsheet, I get a runtime error on the formula.

    This is similar to another problem that was reported on this post:

    http://www.excelforum.com/excel-gene...ml#post4226175

    Can anyone please provide feedback on why these characters disappear and on why the code compiles correctly in VBA but I get the runtime error when trying to run?

    Here is code:

    Please Login or Register  to view this content.
    The active cell formula in red text is the line with the problem.


    Thanks.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Problem with recording a macro with long formula - works in Excel but not in macro

    Have you considered shortening the formula?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-26-2015
    Location
    Lafayette, LA
    MS-Off Ver
    2013
    Posts
    3

    Re: Problem with recording a macro with long formula - works in Excel but not in macro

    Thanks Norie,

    If the formula compiled OK in the VBA editor, shouldn't it be OK how it is?

    Do you know if the reason I am getting a runtime error (when I try to run the macro) is due to the length of the formula or is due to something else? If it is due to the length of the formula, I am not sure how I could shorten it...given that all of the if and or statements need to be there to return the correct "region" in the cell.

    Thanks.

+ 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. DDE Data Recording Macro problem?
    By Weered in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2014, 01:38 PM
  2. [SOLVED] problem in macro recording copy and paste in new excel file,
    By vengatvj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-07-2013, 12:37 AM
  3. [SOLVED] Recording Macro Forumla Too Long
    By seannydj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2012, 09:48 PM
  4. Using a filter while recording a macro problem
    By oniete1997 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2012, 09:56 PM
  5. Any ideas on Macro Recording Problem!
    By squiggler47 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2009, 07:36 AM
  6. Macro recording problem.
    By leats1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2005, 06:05 PM
  7. problem recording macro in excel 2003
    By Davi0 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 05-20-2005, 04:33 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1