+ Reply to Thread
Results 1 to 15 of 15

Clear Content Macro won't run

  1. #1
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Clear Content Macro won't run

    Hello all. I am very new to Macros and have written maybe less than 10 but realize how much help they can be now. That said I need help with one I am writing now its a easy .ClearContents Macro but its giving me fits. I keep getting errors "Subscript out of range (error 9)", "Sub, Function, or Property not defined (Error 35)","Range.ClearContents Method (Excel)" and "Can't execute code in break mode". I've read on this but nothing I do will let me run the Macro and as for the "Break mode" I have no idea what that is. I've tried stopping it. Here is what I'm working on. Any help would be greatly appreciated.


    Please Login or Register  to view this content.
    Last edited by Rigman; 10-24-2016 at 11:33 PM.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Can't get Macro to run

    This is wrong to start with...

    Please Login or Register  to view this content.
    You cannot start another procedure without terminating the previous one with an 'End Sub' or 'End Function'

  3. #3
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Re: Can't get Macro to run

    But this Macro works fine.

    Please Login or Register  to view this content.
    Last edited by Rigman; 10-24-2016 at 08:29 PM.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Can't get Macro to run

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    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, it also maintains VBA formatting.

    Click on Edit to open your thread, 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

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  5. #5
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Re: Can't get Macro to run

    I am in hopes I fixed as asked. I clicked on #as instructed and saved but am not seeing any changes. Please advise. I will visit the rules also. Thank you

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Can't get Macro to run

    Edit your post #1 and post #3 by placing all of your code [ CODE ] between [ /CODE ]

    Also, as you are visiting the forum rules, please take a look at proper thread titles and adjust accordingly. Thanks.
    HTH
    Regards, Jeff

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    Rig manager
    Once you change your thread title, as requested, I'll give you a nice 8 line piece of code that does the same as your code.

  8. #8
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Re: Can't get Macro to run

    Thank you. I greatly appreciate any help. I attached the work book the code if for. Hope that helps anyone that may take the time to help. Again thanks in advance to all.

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    I hope it works on a Mac also.
    This does the same as your code. It also clears cell H4. Did you forget that or does it not need clearing?
    Put this in a regular module and run it.
    Please Login or Register  to view this content.
    What cytop was refering to was that you have two lines at the top that should be only one line. Probably left over from some previous code.
    Delete the "Private Sub Worksheet_Change(ByVal Target As Range)" line


    By: Leith Ross
    Adding the Macro
    Select above macro
    Copy the macro above pressing the keys CTRL+C
    Open your workbook
    Press the keys ALT+F11 to open the Visual Basic Editor
    Press the keys ALT+I to activate the Insert menu
    Press M to insert a Standard Module
    Paste the code by pressing the keys CTRL+V
    Make any custom changes to the macro if needed at this time.
    Save the Macro by pressing the keys CTRL+S
    Press the keys ALT+Q to exit the Editor, and return to Excel.

    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
    Last edited by jolivanes; 10-25-2016 at 12:09 AM. Reason: Add With.....End With

  10. #10
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Re: Can't get Macro to run

    Jolivanes, Thank you very much. That code does work. I can't get it to work with protection on though. Would you have some insight on that? I really need to learn how to write code. Yours is so much easier. Again thank you.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    I never have protection on. But I would say that the reason for protecting a sheet is that it cannot be changed. However, it needs to be changed, that is what the code is all about.
    If you want to have protection, at the beginning of the code you can have a line to unprotect the sheet, need password of course, and at the end a line to protect again.

  12. #12
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    If it is only for the one sheet, this should do it. I have not tried this though.
    Keep in mind that if the code generates a problem that the sheet will be left in unprotected state until you protect it again.
    In this case, RigMan will be your password. Note capitals and small letters.


    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    BTW, I think it might be a good idea to have a folder with Templates of all the excel files you're going to end up with.
    If you do that, you don't really have to worry about clearing the sheets. Just open your template that should have all the formulas etc in it.

  14. #14
    Registered User
    Join Date
    10-24-2016
    Location
    United States
    MS-Off Ver
    2013 PC. 2011 Mac
    Posts
    6

    Re: Can't get Macro to run

    Absolutely. I have a master copy of every spread sheet I build. I've done good with the calculations after I figure out the computer language. Have to re-learn that when I go on a tangent to build a new sheet. The Macro is a whole new beast to me. Again thanks for your help. I'm sure I'll be bugging ya a lot.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,893

    Re: Can't get Macro to run

    Well, sounds like you have it under control.
    Good luck I would say and we'll see you here again.

+ 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. [SOLVED] Define variable in macro than calling that macro inside another macro
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-21-2015, 10:58 AM
  2. Using a macro on workbook1 to create a button in wb2 and assigning macro "wb2!macro"
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2014, 11:39 AM
  3. [SOLVED] Macro to show Which macro didnt work in a nested macro
    By akhileshgs in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-10-2013, 03:21 AM
  4. Perform macro "on open" specific file- store macro in Personal Macro Workbook?
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2012, 12:38 PM
  5. lookup macro, solver macro, realtime macro
    By xelhelp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2011, 06:14 PM
  6. Cannot find macro error when running a macro from a macro in a diffrent workbook.
    By Acrobatic82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2010, 09:22 AM
  7. [SOLVED] Macro calling another Macro: "The macro 'Personal.xls!FindChar"
    By William Benson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2005, 09: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