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
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
Last edited:
