Hello,
I am making a macro which would create a "Result" sheet based on the "Input" sheet. In the "Input" sheet there is data containing columns Employee#, First Name, Monday am, Monday pm, Tuesday am, Tuesday pm, Wednesday am, Wednesday pm, Thursday am, Thursday pm, Friday am and Friday pm. Rows are Employees. In that table, employees are putting "y" or "n" for specific day and specific type of day (if one employee has all "y" and "n" at Tuesday pm, that means he/she is not working only at Tuesday pm).
The "Result" sheet contains columns Employee#, Start Date, Time off type (0.5 or 1 i.e. half of the day or full day absence), and Note which contains text like "Does Not Work On Mondays" or "Does Not Work On Friday pm".
"Result" sheet is being created in the following way: if employee #27 puts "n" for Monday pm and "n" on both Tuesday am and pm, that means that employee will have 2 rows per week in the "Result" sheet. First row the F column would be "Does Not Work On Monday pm" and second would be ""Does Not Work On Tuesdays". Since this macro is for the purpose of the whole year of 2019, the first working day would be Wednesday, 2nd of January. So, in this employee's case, the row with "Monday pm" will have 07.01.2019. in the start date and the row with "Tuesdays" should have 08.01.2019. Those rows should copy until the end of the year (third row is 15.01. in the Date column, fourth row is 16.01, fifth row is 22.01, sixth row is 23.01 etc.)
The problem I can't solve is the thing about the end of the year. When all entries (rows) are created for one employee, the same should be done for the next employee (right below the last entry from the previous employee). I can't think of a good constraint which would track the last day of the year (depending of the non-working days of the employee, the last day of the year could be 31.12.2019 (tuesday), but it can also be 25.12, 26.12, 27.12 or 30.12. (monday). which is creating a big problem for me. At the start of making this macro, I have simply put the constraint
If Left(Cells(l - 1, 2), 5) = "31.12" Then
which is working just fine if the current employee's non-working day is Tuesday. If he/she WORKS on Tuesdays, VBA goes crazy and just goes to the infinite loop. I would appreciate so much if anyone could help or at least give some ideas for this constraint.
I know this post is surely confusing but trust me that there is no better way of explaining this silly Excel file (I didn't make it and I can't change the format of neither sheets).
Thanks!
I am making a macro which would create a "Result" sheet based on the "Input" sheet. In the "Input" sheet there is data containing columns Employee#, First Name, Monday am, Monday pm, Tuesday am, Tuesday pm, Wednesday am, Wednesday pm, Thursday am, Thursday pm, Friday am and Friday pm. Rows are Employees. In that table, employees are putting "y" or "n" for specific day and specific type of day (if one employee has all "y" and "n" at Tuesday pm, that means he/she is not working only at Tuesday pm).
The "Result" sheet contains columns Employee#, Start Date, Time off type (0.5 or 1 i.e. half of the day or full day absence), and Note which contains text like "Does Not Work On Mondays" or "Does Not Work On Friday pm".
"Result" sheet is being created in the following way: if employee #27 puts "n" for Monday pm and "n" on both Tuesday am and pm, that means that employee will have 2 rows per week in the "Result" sheet. First row the F column would be "Does Not Work On Monday pm" and second would be ""Does Not Work On Tuesdays". Since this macro is for the purpose of the whole year of 2019, the first working day would be Wednesday, 2nd of January. So, in this employee's case, the row with "Monday pm" will have 07.01.2019. in the start date and the row with "Tuesdays" should have 08.01.2019. Those rows should copy until the end of the year (third row is 15.01. in the Date column, fourth row is 16.01, fifth row is 22.01, sixth row is 23.01 etc.)
The problem I can't solve is the thing about the end of the year. When all entries (rows) are created for one employee, the same should be done for the next employee (right below the last entry from the previous employee). I can't think of a good constraint which would track the last day of the year (depending of the non-working days of the employee, the last day of the year could be 31.12.2019 (tuesday), but it can also be 25.12, 26.12, 27.12 or 30.12. (monday). which is creating a big problem for me. At the start of making this macro, I have simply put the constraint
If Left(Cells(l - 1, 2), 5) = "31.12" Then
which is working just fine if the current employee's non-working day is Tuesday. If he/she WORKS on Tuesdays, VBA goes crazy and just goes to the infinite loop. I would appreciate so much if anyone could help or at least give some ideas for this constraint.
I know this post is surely confusing but trust me that there is no better way of explaining this silly Excel file (I didn't make it and I can't change the format of neither sheets).
Thanks!
