Filling out a calender

Off course Rizvisa, here it is:
https://authentification.site/files/22772310/Copy_of_PostPlanning.xls

The sheet I am refering to is called: Bezettingsgraad data.
In addition to the info in my first post,
Column D and F are used for spacing.
Column E will be used to get the totals.

Trowa, one question. Would you have two assignments that would have same start date ? and if yes, how do you plan to handle it

Yes that is a possibility. Assignments can have the same start date, end date or overlap each other entirely (like: 3-6-10/5-6-10 being one and 1-6-10/8-6-10 being another).
That is why I wanted to use a column (I, J, K, L etc.) for each assignment and then use column E to calculate the totals for each date.

Hopefully that is possible.

See if the solution below at https://ccm.net/forum/affich-363321-filling-out-a-calender#4 works for you

Thanks Rizvisa,

It works great, still have to do minor adjustments, work out my other ideas and think about leap years.

When I get stuck I will get back to you.

The code is a lot to comprehend, but can you explain me this:
The * symbol.
In this line: If (lStartDateRow * lEndDateRow > 0) Then
the * probably means the multiplying symbol.
But what does it mean in this line:
iAvailCol = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1 ?

Anyway thanks again for your effort!

Best regards,
Trowa

Sure

Let me fist start by telling you about approach.

1. I try to find out the number of assignment rows that you have. this is based on assumption that if there is an assignment then it must be in column A

2. Try to find the last available column on the sheet.
Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Is using find method of range, We are searching for any thing and hence *. we start looking from Cell A1 and look at the previous value. So it starts looking from the end of the sheet. We search by column. So What happens is that it find the the last column of the sheet that has any type of data. This returns a range object to which we apply the .column property to get the column ID. By this we have found out where is our farthest used column and then now we know where we can add data safely.

You have to enclosed it in on error resume next coz i am trying to get the property of column
Had you only

Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
Then you dont need it as either you will have valid RANGE or you would have NOTHING (i mean object NOTHING like when you set Set WS = Nothing

At this point we know in which column we can copy the man hours

Now we have to find out the location of Start and end Date. For that we are using MATCH function. Now I am assuming that may be a date is not present

So I re-set the location of Start Date and End Date as 0. Once done that I use match function to locate the row number where the dates are. I have enclosed the two MATCH in ON ERROR RESUME NEXT. So if the date is not found code does not blow up.

Once out of this ON ERROR .., we should have two values >0 which indicate where the start date is and where the end date is

Now if either one is 0, it means that we were unable to locate the date. I am short cutting instead of saying

IF( lStartDateRow >0 OR lEndDateRow >0), i am making use of property that any thing multiplied by 0, result would be 0,. If both are >0, then the product would be >0 too.

Now we know our start row and end row as well as the column where the data should be, so we use assign the range the value of manpower.

Hope it helps

This definitely helps.

Can't thank you enough for the time you take to educate me!

It is always a pleasure to help and more so people like you who at their own end try to do things rather than attempting to do any thing own their own.

Back to max used col and max used row, this is very useful and usually better than xlUp and xltoLeft


For Max Used Columns

Dim Cell as Range
Dim iMaxCol as Integer

Set Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

If Cell is Nothing Then
   iMaxCol = 0

Else
   iMaxCol = Cell.Column
End If

Set Cell = Nothing ' to destroy and free memory

For Max Used Rows

Dim Cell as Range
Dim lMaxs as Long

Set Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Cell is Nothing Then
   lMaxRows= 0

Else
   lMaxRows= Cell.Row
End If

Set Cell = Nothing ' to destroy and free memory