+ Reply to Thread
Results 1 to 6 of 6

Don't Merge Already Merged Cells

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Don't Merge Already Merged Cells

    I have one last "error" in my spreadsheet that I have no idea how to address. Quick explanation:

    My spreadsheet creates a graphical schedule based on the name you select. Each cell counts as a 30-minute block of time, with the times rounding up to the nearest half hour for class start times, and rounding down for class end times (so if a class starts at 8:17, the time is rounded to 8:00. If it ends at 8:17, it rounds to 8:30). I have a loop that takes the class times (converted into integers) and merges a range of cells based on these values. The code is here:

    Please Login or Register  to view this content.
    It works *beautifully* in 95% of the cases, but I have one hiccup that I'm not sure how to address. Let's say you have two classes back-to-back, one that ends at 8:10 and the next class begins at 8:15. Based on rounding, that means the first class ends at 8:30 and the next class begins at 8:00, so they overlap. As a result, the cells for both classes end up being merged together so one of the classes goes away. Is there a way to make VBA check to see if a cell is merged , and if so, merge only the cells within the range that aren't already merged?

    I could solve this by rounding everything up or down, but then the class contact hours would be incorrect. I just need to find some way to keep the overlapping classes from merging together if that's possible. Any input would be greatly appreciated!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Don't Merge Already Merged Cells

    Not sure I follow everything, but try something like this.
    The code checks if the first cell in the range is already merged. If yes, it offsets and resizes the range by 1 cell.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 01-14-2018 at 11:29 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Don't Merge Already Merged Cells

    I don't quite follow the logic. If classes could start at 08:15, why don't you have 15 min blocks of time and round accordingly?

  4. #4
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Don't Merge Already Merged Cells

    Jolivanes - That is a question that has a very complicated answer. In short, to meet required contact time you take the semester length, days of the class, and depending on breaks, holidays, etc., sometimes you have an unusual class times to ensure you meet the required contact time. For instance, I have a MWF class that ends at different times on each day (but begins at the same time). Long ago, it was decided 30 min blocks would be used so professors would have time to move rooms, use the restroom, etc., in the "rounding time". Obviously, from the point of schedule creating it can throw a monkey wrench in things. Hopefully that makes sense.

    AlphaFrog - I'm about to call it a night but I'll tinker with this tomorrow unless I get a second wind. FYI, here is a sample output of one schedule where this has occurred. The first page is the graphical schedule, and the second page is the detailed schedule. From the legend on the graphical schedule you see there should be four distinct classes. However, only three colors are on the schedule. Basically, the late class (ELN 150) is getting merged with the earlier class (ELC 131). I don't want the ultimate end time to change... hopefully this makes sense.

    The file Sample Schedule.pdf can be found here:

    https://www.dropbox.com/sh/8w0p3gakg...5-gC6Oyva?dl=0

    Thanks!

  5. #5
    Registered User
    Join Date
    10-08-2012
    Location
    NC
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Don't Merge Already Merged Cells

    Okay... here is the code as I revised it.

    Please Login or Register  to view this content.
    I'm not sure what's happening, but it gives me an error when I try to set the variable rngClass.

    Please Login or Register  to view this content.
    The values for each variable inside of the line are all populated (StartClass, EndClass and Column), so it seems that it should work. I'll keep tinkering... any thoughts?

    EDIT: Nevermind!!! Geez... I can't tell you how many times I looked over this code and missed that I didn't SET rngClass. It works perfectly--thank you so much!
    Last edited by JonathanEngr; 01-15-2018 at 01:12 PM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Don't Merge Already Merged Cells

    Please Login or Register  to view this content.
    If Faculty Schedule is not the active sheet, then it would error because the two Cells commands don't have a sheet qualifier and would default to the Active sheet which is in conflict with the .Range sheet qualifier.


    Try this. It qualifies the Range and Cells references with the same sheet.
    Please Login or Register  to view this content.

+ 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. Mysterious Merge: columns all merged into single cells
    By emeraldzippy in forum Excel General
    Replies: 4
    Last Post: 07-07-2016, 08:41 AM
  2. Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  3. automatically fit an image into merged cells for full width of merged cells
    By Marcin4111 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-18-2014, 03:12 PM
  4. Macro to find matching cells, then merge, and add data to show row was merged
    By hambly in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2013, 02:24 PM
  5. Replies: 1
    Last Post: 06-28-2012, 11:53 AM
  6. [SOLVED] Merge Cells When Other Merged Cells Receive Specific Data
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 39
    Last Post: 04-03-2012, 10:34 AM
  7. how do i link merged cells to a merged cell in another worksheet.
    By ibbm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2006, 06:45 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