Filling out a calender

Closed
Trowa - Jun 1, 2010 at 09:34 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Jun 8, 2010 at 03:59 PM
Hello,

I would like to know how many people are occupied during a period.

On my sheet I use column A:C starting at row 3 to input the startdate, the enddate and how many people are busy each day during that period.
Colum H contains every day of the year.
Now if I have an assignment which starts at 1-jun-10 and ends at 6-jun-10 and occupies 15.6 people everyday, I would like to use column I to put in 15.6 at the same row as the date 1-jun-10 of column H. Fill out 15.6 on every row untill 6-jun-10.
For the next assignment use the next column (column J) and so on.

Once I know how to do that I can add all the columns together, exclude the weekends and holidays, create a graph and I will be able to see how many people are occupied on any date.

A better/cleaner/shorter solution is always welcome.

Best regards,
Trowa


2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 1, 2010 at 12:36 PM
Hi Trowa, could you post a sample book. Though it is explained, I am just unable to comprehend.
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 3, 2010 at 09:14 AM
Trowa, one question. Would you have two assignments that would have same start date ? and if yes, how do you plan to handle it
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 3, 2010 at 09:49 AM
See if the solution below at https://ccm.net/forum/affich-363321-filling-out-a-calender#4 works for you
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 3, 2010 at 09:46 AM
Try this Trowa

Sub FillSchedule() 
Dim lAssignmentRows As Long ' last assignment row 
Dim lAssignmentStart As Long 'assignment starts at row 
Dim lRow As Long ' for counting rows 

Dim lStartDateRow As Long 'row where the project starts date is 
Dim lEndDateRow As Long 'row where the project end date is 
Dim iAvailCol As Integer ' which column is available for next fill in 

    Sheets("Bezettingsgraad data").Select

    lAssignmentStart = 3 
     
    lAssignmentRows = Cells(Rows.Count, "A").End(xlUp).Row 
     
    'find the maximum used column and then add 1 to it to start filling in the data 
    iAvailCol = 0 
    On Error Resume Next 
        iAvailCol = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1 
    On Error GoTo 0 

             
    For lRow = lAssignmentStart To lAssignmentRows 
     
        If ((Cells(lRow, 1) <> "") And (Cells(lRow, 2) <> "")) Then 
         
            lStartDateRow = 0 
            lEndDateRow = 0 
             
            On Error Resume Next 
                lStartDateRow = WorksheetFunction.Match(Cells(lRow, "A"), Range("H:H"), 0) 
                lEndDateRow = WorksheetFunction.Match(Cells(lRow, "B"), Range("H:H"), 0) 
            On Error GoTo 0 
             
            'make sure both are more than 0 
            If (lStartDateRow * lEndDateRow > 0) Then 
             
                Range(Cells(lStartDateRow, iAvailCol), Cells(lEndDateRow, iAvailCol)).Value = Cells(lRow, "C") 
                iAvailCol = iAvailCol + 1 
            End If 
             
        End If 

    Next 
End Sub
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 7, 2010 at 09:55 AM
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
0
This definitely helps.

Can't thank you enough for the time you take to educate me!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 8, 2010 at 03:59 PM
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
0