Random value must be unique depending on date range

Retroshift

Board Regular
Joined
Sep 20, 2016
Messages
119
Office Version
  1. 2019
Platform
  1. 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?

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
 

Attachments

  • Month sheet example - random values.jpg
    Month sheet example - random values.jpg
    80.4 KB · Views: 16

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
This is what I got so far but I cannot seem to make every single week contain unique random values. Any ideas?

VBA Code:
                    If rng = vbMonday Then
                    Set weekrange = rng.Offset(3).Resize(1, 5)
                    ElseIf rng = vbTuesday Then
                    Set weekrange = rng.Offset(3).Resize(1, 4)
                    ElseIf rng = vbWednesday Then
                    Set weekrange = rng.Offset(3).Resize(1, 3)
                    ElseIf rng = vbThursday Then
                    Set weekrange = rng.Offset(3).Resize(1, 2)
                    ElseIf rng = vbFriday Then
                    Set weekrange = rng.Offset(3)
                    End If
                    For Each wkrng In weekrange
                    Randomval = Ary(WorksheetFunction.RandBetween(1, k), 1)
                    Do While Application.WorksheetFunction.CountIf(weekrange, Randomval) >= 1
                    Randomval = Ary(WorksheetFunction.RandBetween(1, k), 1)
                    Loop
                    wkrng.Value = Randomval
                    Next
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top