# Random Placement-VBA

#### vishal0589

##### Board Regular
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.

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

dropcanvas - instant drag and drop sharing - canvas view

Thanks & Regards,
CMA Vishal Srivatsava

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
No help yet

Replies
6
Views
343
Replies
5
Views
142
Replies
6
Views
250
Replies
6
Views
365
Replies
4
Views
717

1,207,423
Messages
6,078,440
Members
446,338
Latest member
AliB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back