Retroshift
Board Regular
- Joined
- Sep 20, 2016
- Messages
- 119
- Office Version
- 2019
- Platform
- Windows
Hello, I have a button that creates 12 month sheets for a specific year that is entered into an inputbox. The code below enters values (coming from a master sheet list) randomly every workday per month sheet created.
I would like to have only unique random values per week, and a minimum of 2 and maximum of 4 of each value per month range.
I added an example screenshot of a month sheet where my RandBetween code wrongly enters the same values consecutively (highlighted in red).
Anyone knows how to alter the code accordingly?
I would like to have only unique random values per week, and a minimum of 2 and maximum of 4 of each value per month range.
I added an example screenshot of a month sheet where my RandBetween code wrongly enters the same values consecutively (highlighted in red).
Anyone knows how to alter the code accordingly?
VBA Code:
For Each rng In Range("B1").Resize(1, Day(DateSerial(yr, i + 1, 1) - 1))
If Weekday(rng) <> 1 And Weekday(rng) <> 7 And CLng(rng) <> Application.Lookup(CLng(rng), datearray) Then
Range(rng.Rows(4), rng.Rows(4)).Value = Ary(WorksheetFunction.RandBetween(1, k), 1)
End If
Next