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



## noveske (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 3, 2023)

bebo021999 said:


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




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 (Jan 3, 2023)

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 (Jan 6, 2023)

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 (Sunday at 10:43 PM)

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
```


----------

