+ Reply to Thread
Results 1 to 13 of 13

Cannot Format with Certain VBA Code

  1. #1
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Cannot Format with Certain VBA Code

    Hi Everyone,

    For some reason, when I run the code below, I am not able to format the entire work book to do certain functions such as auto fit columns and rows for the entire workbook.

    I have tried to incorporate a "Select All" sheets macro but that still is not working

    If there a way to possibly adjust this code so that I will be able to have a macro following it that will be able to use the formatting I wish to be applied?

    Thanks for the help.

    Please Login or Register  to view this content.

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

    Re: Cannot Format with Certain VBA Code

    Why not add to the existing loop to do the formatting on each sheet?

    For example this, which would go just before Next Ndx.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    I tried that and it does not seem to work.

    Would I be able to add the code that contains all of the formatting after
    Please Login or Register  to view this content.

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

    Re: Cannot Format with Certain VBA Code

    You could but it's probably not a good idea because in the existing code you are adding borders, inserting rows etc.

    How did you try what I posted?

  5. #5
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    Yes I tried what you posted and the problem is that there are merged cells on the sheet so its seems that the auto fit isn't working well

    What my formatting macro did was unmerge them, placed the cells outside the range I needed auto-fitted, moved them back to their original cells and then re-merged

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

    Re: Cannot Format with Certain VBA Code

    What formatting macro are you referring to?

  7. #7
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    Please Login or Register  to view this content.

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

    Re: Cannot Format with Certain VBA Code

    Try this.
    Please Login or Register  to view this content.
    PS Have you considered not using merged cells?

    As far as I can figure out all the ranges that are merged consist of a single row and if that's the case you could use the Center across selection alignment to get the same visual effect as merging.

  9. #9
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    I am getting an error message when trying to run this code.

    That's not a bad idea I could try and use the center across function. Would any error appear when running the macro?

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

    Re: Cannot Format with Certain VBA Code

    What error message?

    Changing to center across selection might not prevent errors but it should make things easier, for example you wouldn't need to unmerge, format and then merge.

  11. #11
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    Error Message.PNG

    Image of the error message is attached Error '424' object required

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

    Re: Cannot Format with Certain VBA Code

    What I posted was more to give you a general idea of how to do it.

    That was a fair amount of code, some of it unnecessary, so it's bound to have a few typos/omissions.

    Here it is again, this version has been tested but only in a blank workbook.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-11-2013
    Location
    new york,ny
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Cannot Format with Certain VBA Code

    Absolutely I understand and appreciate the help

    Would it be easier if I provided you with a sample of a worksheet in a workbook that may have 200+ worksheets?

    Attached is one and has the "Header" tab which is constant on each run

    Example Header.xlsx

+ 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. Code to Force Format / Prevent Format Changes
    By imogul in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2016, 04:20 AM
  2. [SOLVED] help on vba code to change the error date format as standard format mm/dd/yyyy
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-13-2013, 01:10 AM
  3. Need Formula/VBA Code to convert date in String Format to Normal Date format
    By Karthik Sen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2012, 04:54 AM
  4. Replies: 0
    Last Post: 10-06-2006, 09:05 AM
  5. zip code format
    By Rachel in forum Excel General
    Replies: 2
    Last Post: 01-11-2005, 04:19 PM

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