Random Placement-VBA

[vishal0589]

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.

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 If

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 link for reference. Please help me out.

dropcanvas - instant drag and drop sharing - canvas view


Thanks & Regards,
CMA Vishal Srivatsava

 

[vishal0589]

No help yet