Find next blank column without merged cells

ShoYnn

New Member
Joined
Mar 20, 2019
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
I have a calendar that is used to schedule jobs. The days are done by rows and the scheduled jobs by columns. My current calendar allows for 8 jobs to be scheduled per week. To schedule a job the user types the start date and end date then clicks a button that will then merge that range and put the job title in it. The sub looks at the range specified and if it is not blank it moves over to the next column to add it there and this works up until the 8th job where it will then tell the user that the week is full.

All of this works perfectly IF the next job that is added starts on the same day of the week (i.e. both start on Monday). However, because of the merged cells, if the next job starts on Tuesday it will just get eaten by the first job of that week! I have found a way to get it to check the previous column for either a blank(if they start on the same day) or if the range includes merged cells which was a big leap for me. The issue I am running into now is that the code I am using to find the next column is not taking the merged cells in the previous column into account, it is just looking to see what is the next column with a specific row that is blank. This is the issue again because if the previous job started on Monday the next job starts on Tuesday then this formula is looking for the first column with a blank in Tuesday, which is the column that has the job that starts on Monday! How could I modify it so that it looks for the first blank row AND first non-merged row?

Nxtcol = Cells(Jobrow, Columns.Count).End(xlToLeft).Column + 1

Where Jobrow is a string that finds the row that is associated with the start date of the job to be added
 

Attachments

  • Excel.png
    Excel.png
    63.3 KB · Views: 6
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ShoYnn

New Member
Joined
Mar 20, 2019
Messages
45
Office Version
  1. 2016
Platform
  1. Windows
I just thought of another issue that I will run into. Say the first job starts on Tuesday. Any of the following jobs that start on Monday will get created over that Tuesday job as it will look for the first blank Monday! So it seems that for this to work properly I would need a formula like the one above that scans a range for the next blank instead of just the row... Crap!
 

Forum statistics

Threads
1,147,632
Messages
5,742,228
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top