Vishal Srivastava
Member
Hi Friends,
I need your help. I am working on attendance register where attendance being generated based on fixed counts on random basis.
Actually every thing works fine except Weekly Offs (W/O).
As per requirement, Weekly Offs is not fixed on particular date and Weekly Offs should be randomly placed within any date of a month based on fixed count.
For example,
If total 4 Weekly off is allowed to any staff then W/O date should be random one i.e. 1,6,15,25.
One more thing I would like to mention that gap between two random dates must be greater than or equals to 5.
I am using following code.
This code works great, but problem is that dates are fixed for almost every staffs. e.g. 1,5,10,15 for maximum staff.
What I want, different dates should be picked for every employee (Some dates may be similar for some staff):
for example:
Emp-1 = 1,5,10,15
Emp-2 = 3,8,13,19
Emp-3 = 1,7,12,23
I have also attached the sample sheet for reference. Please help me out.
Thanks & Regards,
CMA Vishal Srivatsava
I need your help. I am working on attendance register where attendance being generated based on fixed counts on random basis.
Actually every thing works fine except Weekly Offs (W/O).
As per requirement, Weekly Offs is not fixed on particular date and Weekly Offs should be randomly placed within any date of a month based on fixed count.
For example,
If total 4 Weekly off is allowed to any staff then W/O date should be random one i.e. 1,6,15,25.
One more thing I would like to mention that gap between two random dates must be greater than or equals to 5.
I am using following code.
Code:
If WorksheetFunction.CountIf(Range("L" & Rw & ":AO" & Rw), "W/O") < Cells(Rw, 46).Value Then
tot = WorksheetFunction.RandBetween(12 + Cells(Rw, 59).Value, 41)
If tot Mod 5 = 0 Then
If Cells(Rw, tot).Value = "A" Or Cells(Rw, tot).Value = "CL" Or Cells(Rw, tot).Value = "H" Or Cells(Rw, tot).Value = "H/P" Or Cells(Rw, tot).Value = "EL" Or Cells(Rw, tot).Value = "PP" Then
Else
Cells(Rw, tot).Value = "W/O"
End If
End If
End IfThis code works great, but problem is that dates are fixed for almost every staffs. e.g. 1,5,10,15 for maximum staff.
What I want, different dates should be picked for every employee (Some dates may be similar for some staff):
for example:
Emp-1 = 1,5,10,15
Emp-2 = 3,8,13,19
Emp-3 = 1,7,12,23
I have also attached the sample sheet for reference. Please help me out.
Thanks & Regards,
CMA Vishal Srivatsava
Last edited:
