+ Reply to Thread
Results 1 to 7 of 7

VBA breaks my fromula

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    10

    VBA breaks my fromula

    Hey i use this part of macro to move cells to left but when it runs my formula its ruinned. Macro wont run if sheet is protected.


    Please Login or Register  to view this content.
    Formula

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


    After macro:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by PumSese; 05-27-2019 at 05:26 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,354

    Re: VBA breaks my fromula

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: VBA breaks my fromula

    Hi,
    A remark, avoid using select and activate,it's rarely necessary and it slows down your code.
    This
    Please Login or Register  to view this content.
    does the same as
    Please Login or Register  to view this content.
    To run macro on a protected sheet,example with your code.
    Change pw with your real pasword.
    Please Login or Register  to view this content.
    For the formula problem.
    What happens when you delete the $ in the formula?
    Like this, but for the complete formula.
    M5:M55 instead of M$5:M$55
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-28-2018
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    10

    Re: VBA breaks my fromula

    Thank you for advice. I did it at first without $. This is example picture how it looks after macro.
    Capture.PNG

  5. #5
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: VBA breaks my fromula

    Why you want to delete blank cell on M5:Z55? When you delete a range which is reference of a formula, the formula will be broken. That is normal.

  6. #6
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,439

    Re: VBA breaks my fromula

    VBA does not ruin the formula.

    this is standard thing that Excel does when you delete lines in the range that is inside a formula. This is to avoid circular references or the formula starting to include data outside the originally assigned area causing wrong results.
    The $ makes sure the range does not change size when you drag or copy the formula itself. It does not guarantee the range stays the same if you delete rows within the specified range.

    If you do not want have blank rows but keep the formula it would be better to do hide the blank rows then to delete them.


    If you post an example excel file (and not screenshots) and explain a bit more what you want to achieve then I am sure we will be able to help you come up with a working solution for your case.

  7. #7
    Registered User
    Join Date
    06-28-2018
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    10

    Re: VBA breaks my fromula

    Fixed it by using indirect in formula. Thank you all for help.
    Last edited by PumSese; 05-29-2019 at 05:01 AM.

+ 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. Formulas and Line Breaks - inserting mid-string & removing excess breaks
    By MattDay2u in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-24-2017, 09:54 AM
  2. Clear all page breaks, then insert new breaks at change in data
    By Alex0929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2014, 03:12 PM
  3. [SOLVED] If SUM of fromula is Less than zero.
    By brutski in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 04:01 PM
  4. Hard page breaks being replaced by soft breaks in the wrong place
    By JDavies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2008, 10:28 AM
  5. Need a fromula
    By comotoman in forum Excel General
    Replies: 3
    Last Post: 10-03-2005, 12:05 PM
  6. Hard breaks in text to soft breaks in Excel
    By tbailey in forum Excel General
    Replies: 6
    Last Post: 07-27-2005, 10:05 PM
  7. Hard breaks in text to soft breaks in Excel
    By tbailey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2005, 12:05 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