Filling cells based on drop down selection and random number generation in order.

[noveske]

Got stumped again on something simple. Being an overthinker with lack of sleep just has me running in circles. The complete task is a bit more involved, but if I could just get some help with the basics, I should be able to finish it out.

There are 3 steps I am trying to make work together.
It's 2 selections and an automated process.

Option Sets:
1, 2, 3, 4, 5
A, B

Select a number 1-5.

Then.
Select A or B.

If select A,
cells A4:A11 are filled with A1,
cells A12:A19 are filled with A2,
If select B,
cells A4:A11 are filled with B1,
cells A12:A19 are filled with B2,

When A or B is selected (so this happens everytime no matter the selection).
cells C4:C11 are filled with a random number between 01-32,
cells C12:C19 are filled with a random number between 33-64,

But if option 5 is selected from the first step then, (the range changes)
cells C4:C11 are filled with a random number between 01-36,
cells C12:C19 are filled with a random number between 44-75.

For the finer number details:
01-09, I need the 0 to stay. Thought it might be show zero values, but it is a value.
I thought I could format to Text, then realized it's the formula that would negate that.
I tried with =RANDBETWEEN(1,32) with 01, it would just revert to 1.

For cells C4:C11, C12:C19 I do need them to be sorted lowest to greatest.
Was tinkering between having that done when generated or adding another step to sorting after generated.

 

[noveske]

Lastly the random numbers can not repeat.
I realize after generating the random numbers, they would have to be copied, sorted, then displayed.
I'm thinking I could just have a sheet of values which lists all the options and also the random. Just having trouble tying it together with a button push.

 

[bebo021999]

Not clear for me, but try:
Assum Cell E1 is 1-5
E2 is A or B

Right clik on tab name, ViewCode, then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r&, k&, lim1&, lim2&, lim3&, arr(1 To 16, 1 To 1)
Dim dic1 As Object: Set dic1 = CreateObject("Scripting.dictionary")
Dim dic2 As Object: Set dic2 = CreateObject("Scripting.dictionary")
If Intersect(Target, Range("E1:E2")) Is Nothing Then Exit Sub
If Target.Address(0, 0) = "E2" Then
    Select Case Target.Value
        Case "A"
            Range("A4:A11").Value = Range("A1").Value
            Range("A12:A19").Value = Range("A2").Value
        Case "B"
            Range("A4:A11").Value = Range("B1").Value
            Range("A12:A19").Value = Range("B2").Value
    End Select
Else
    If Target.Value < 5 Then
        lim1 = 32: lim2 = 33: lim3 = 64
    Else
        lim1 = 36: lim2 = 44: lim3 = 75
    End If
    Randomize
    Do
        r = Int(Rnd() * lim1) + 1
        If Not dic1.exists(r) Then
            k = k + 1
            dic1.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 8
    Do
        r = Int(Rnd() * lim3) + 1
        If Not dic2.exists(r) And r >= lim2 Then
            k = k + 1
            dic2.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 16
    With Range("C4:C19")
        .Value = arr
        .Sort Range("C3")
    End With
End If
End Sub

If it does not work, try to attach a sample worksheet via, i,e, gg drive.

Book1.xlsm

drive.google.com

 

[noveske]

Not clear for me, but try:
Assum Cell E1 is 1-5
E2 is A or B

Right clik on tab name, ViewCode, then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r&, k&, lim1&, lim2&, lim3&, arr(1 To 16, 1 To 1)
Dim dic1 As Object: Set dic1 = CreateObject("Scripting.dictionary")
Dim dic2 As Object: Set dic2 = CreateObject("Scripting.dictionary")
If Intersect(Target, Range("E1:E2")) Is Nothing Then Exit Sub
If Target.Address(0, 0) = "E2" Then
    Select Case Target.Value
        Case "A"
            Range("A4:A11").Value = Range("A1").Value
            Range("A12:A19").Value = Range("A2").Value
        Case "B"
            Range("A4:A11").Value = Range("B1").Value
            Range("A12:A19").Value = Range("B2").Value
    End Select
Else
    If Target.Value < 5 Then
        lim1 = 32: lim2 = 33: lim3 = 64
    Else
        lim1 = 36: lim2 = 44: lim3 = 75
    End If
    Randomize
    Do
        r = Int(Rnd() * lim1) + 1
        If Not dic1.exists(r) Then
            k = k + 1
            dic1.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 8
    Do
        r = Int(Rnd() * lim3) + 1
        If Not dic2.exists(r) And r >= lim2 Then
            k = k + 1
            dic2.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 16
    With Range("C4:C19")
        .Value = arr
        .Sort Range("C3")
    End With
End If
End Sub

If it does not work, try to attach a sample worksheet via, i,e, gg drive.

Book1.xlsm

drive.google.com

Thanks.

This should work.

Random.xlsm

Microsoft Excel Workbook

1drv.ms

You would have to select a number and a color.

If 1, then fill A4:A19 with 1.
If 2, then fill A4:A19 with 2.

Then fill C4:C10 with a random number within a range.
But if Black is selected, it would show a different range.
All other would show the default range.

Then have the random numbers sorted in order lowest to greatest is possible.

 

[bebo021999]

This should be in sheet "Random" module (not in workbook like it be)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r&, k&, lim1&, lim2&, lim3&, arr(1 To 16, 1 To 1)
Dim dic1 As Object: Set dic1 = CreateObject("Scripting.dictionary")
Dim dic2 As Object: Set dic2 = CreateObject("Scripting.dictionary")
If Intersect(Target, Union(Range("E2"), Range("H2"))) Is Nothing Then Exit Sub
If Target.Address(0, 0) = "H2" Then
    Range("A4:A19").Value = Range("H2").Value
Else
    If Target.Value <> "Black" Then
        lim1 = 32: lim2 = 33: lim3 = 64
    Else
        lim1 = 36: lim2 = 44: lim3 = 75
    End If
    Randomize
    Do
        r = Int(Rnd() * lim1) + 1
        If Not dic1.exists(r) Then
            k = k + 1
            dic1.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 8
    Do
        r = Int(Rnd() * lim3) + 1
        If Not dic2.exists(r) And r >= lim2 Then
            k = k + 1
            dic2.Add r, ""
            arr(k, 1) = r
        End If
    Loop Until k = 16
    With Range("C4:C19")
        .Value = arr
        .Sort Range("C3")
    End With
End If
End Sub

 

[noveske]

Thanks a bunch. Functional.

For the numbers, how would I have them fill in every 2?
Basically would generate 8 numbers in pairs.
1
1
4
4
6
6
12
12
30
30


I just get target mismatch on:
If Target.Value <> "Black" Then
Just fine tuning now.

 

[bebo021999]

Try again:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r&, k&, lim1&, lim2&, lim3&, arr(1 To 16, 1 To 1)
Dim dic1 As Object: Set dic1 = CreateObject("Scripting.dictionary")
Dim dic2 As Object: Set dic2 = CreateObject("Scripting.dictionary")
If Intersect(Target, Union(Range("E2"), Range("H2"))) Is Nothing Then Exit Sub
If Target.Address(0, 0) = "H2" Then
    Range("A4:A19").Value = Range("H2").Value
Else
    If Target.Value <> "Black" Then
        lim1 = 32: lim2 = 33: lim3 = 64
    Else
        lim1 = 36: lim2 = 44: lim3 = 75
    End If
    Randomize
    Do
        r = Int(Rnd() * lim1) + 1
        If Not dic1.exists(r) Then
            k = k + 1
            dic1.Add r, ""
            arr(k, 1) = r
            k = k + 1 ' new added
            arr(k, 1) = r ' new added
        End If
    Loop Until k = 8
    Do
        r = Int(Rnd() * lim3) + 1
        If Not dic2.exists(r) And r >= lim2 Then
            k = k + 1
            dic2.Add r, ""
            arr(k, 1) = r
            k = k + 1 ' new added
            arr(k, 1) = r ' new added
        End If
    Loop Until k = 16
    With Range("C4:C19")
        .Value = arr
        .Sort Range("C3")
    End With
End If
Range("A1").Select
End Sub