# select cell, generate random numbers, place numb. in new cel

#### ms_metis

##### New Member
Hello, got a couple of questions for the group.

I have a number in B4 that will range from 1 to 7.
I need to randomly generate numbers (from 1 to 6) based on the number in B4.

If the number in B4 is:
1, I need 0 placed in cell B25 to B30

2, I need 1 number randomly generated (from 1 to 6) and placed in B25

3, I need 2 unique numbers randomly generated (from 1 to 6) and placed in B25-B26

4, I need 3 unique numbers randomly generated (from 1 to 6) and placed in B25-B27

5, I need 4 unique numbers randomly generated (from 1 to 6) and placed in B25-B28

6, I need 5 unique numbers randomly generated (from 1 to 6) and placed in B25-B29

7, I need 1 placed in Cell B25, 2 placed in cell B26, 3 placed in cell B27, 4 placed in cell B28, 5 placed in cell B29, and 6 placed in cell B30.

I think this must be done using VB. I have a small knowledge of VB, but I do have some books....

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hello,

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Do Until MY_FIRST <> MY_SECOND And MY_SECOND <> MY_THIRD And MY_THIRD <> MY_FOURTH And _
MY_FOURTH <> MY_FIFTH And MY_FIFTH <> MY_SIXTH
MY_FIRST = Int((7 * Rnd) + 1)
MY_SECOND = Int((7 * Rnd) + 1)
MY_THIRD = Int((7 * Rnd) + 1)
MY_FOURTH = Int((7 * Rnd) + 1)
MY_FIFTH = Int((7 * Rnd) + 1)
MY_SIXTH = Int((7 * Rnd) + 1)
Loop
Select Case Range("B4").Value
Case 1
Range("B25:B30").Value = 0
Case 2
Range("B25").Value = MY_FIRST
Case 3
Range("B25").Value = MY_FIRST
Range("B26").Value = MY_SECOND
Case 4
Range("B25").Value = MY_FIRST
Range("B26").Value = MY_SECOND
Range("B27").Value = MY_THIRD
Case 5
Range("B25").Value = MY_FIRST
Range("B26").Value = MY_SECOND
Range("B27").Value = MY_THIRD
Range("B28").Value = MY_FOURTH
Case 6
Range("B25").Value = MY_FIRST
Range("B26").Value = MY_SECOND
Range("B27").Value = MY_THIRD
Range("B28").Value = MY_FOURTH
Range("B29").Value = MY_FIFTH
Range("B30").Value = MY_SIXTH
Case 7
Range("B25").Value = 1
Range("B26").Value = 2
Range("B27").Value = 3
Range("B28").Value = 4
Range("B29").Value = 5
Range("B30").Value = 6

End Select
End If
End Sub``````

Put this code into the relevant sheet code window, as it will be activated when the cell value in B4 is changed.

If you want to run it from a command button, put the code into a module.

Thanks but I noticed one problem. I can't have the same number more than once.

Does anyone know how to amend this code to make it so that each number is unique. That is, each number can only appear once.

Thanks

Hello,

Have amended the code with the code you supplied

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Range("B25:B31").ClearContents
Do Until MY_FIRST <> MY_SECOND And MY_SECOND <> MY_THIRD And MY_THIRD <> MY_FOURTH And _
MY_FOURTH <> MY_FIFTH And MY_FIFTH <> MY_SIXTH
MY_FIRST = Int((7 * Rnd) + 1)
MY_SECOND = Int((7 * Rnd) + 1)
MY_THIRD = Int((7 * Rnd) + 1)
MY_FOURTH = Int((7 * Rnd) + 1)
MY_FIFTH = Int((7 * Rnd) + 1)
MY_SIXTH = Int((7 * Rnd) + 1)
Loop
Select Case Range("B4").Value
Case 1
Range("B25:B30").Value = 0
Case 7
Range("B25").Value = 1
Range("B26").Value = 2
Range("B27").Value = 3
Range("B28").Value = 4
Range("B29").Value = 5
Range("B30").Value = 6
Case 2 To 6
n = Range("B4").Value
Dim r, num(6), used(6)
For x = 1 To n: used(x) = 0: Next
For x = 1 To n
1              r = Int(Rnd * 6) + 1
If used(r) = 1 Then GoTo 1
num(x) = r: used(r) = 1
Cells(24 + x, 2) = num(x)
Next
End Select
End If
End Sub``````

Great...one more problem. If B4 is 6, only 5 numbers are to be chosen.

So, would I change the code to:

Case 2 To 6
n = Range("B4").Value
Dim r, num(5), used(6)
For x = 1 To n: used(x) = 0: Next
For x = 1 To n
1 r = Int(Rnd * 6) + 1
If used(r) = 1 Then GoTo 1
num(x) = r: used(r) = 1
Cells(24 + x, 2) = num(x)
Next

I think I got it. So many thanks.... This is the code I'm using (se below).

If I want my_sixth to be 0 for case 6, what would I write?

I also have another question, I want to learn VB. I would use it for stuff like this and to give surveys (randomizing questions and having skip to patterns and storing the data). I would love to one day do it over the internet sometimes. How do you suggest I start?

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B25:B31").ClearContents
Do Until MY_FIRST <> MY_SECOND And MY_SECOND <> MY_THIRD And MY_THIRD <> MY_FOURTH And _
MY_FOURTH <> MY_FIFTH And MY_FIFTH <> MY_SIXTH
MY_FIRST = Int((7 * Rnd) + 1)
MY_SECOND = Int((7 * Rnd) + 1)
MY_THIRD = Int((7 * Rnd) + 1)
MY_FOURTH = Int((7 * Rnd) + 1)
MY_FIFTH = Int((7 * Rnd) + 1)
MY_SIXTH = Int((7 * Rnd) + 1)
Loop
Select Case Range("B4").Value
Case 1
Range("B25:B30").Value = 0
Case 7
Range("B25").Value = 1
Range("B26").Value = 2
Range("B27").Value = 3
Range("B28").Value = 4
Range("B29").Value = 5
Range("B30").Value = 6
Case 2 To 6
n = Range("B4").Value
Dim r, num(5), used(6)
For x = 1 To 5: used(x) = 0: Next
For x = 1 To 5
1 r = Int(Rnd * 6) + 1
If used(r) = 1 Then GoTo 1
num(x) = r: used(r) = 1
Cells(24 + x, 2) = num(x)
Next
End Select
End If
End Sub

Hello,

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Range("B25:B31").ClearContents
Do Until MY_FIRST <> MY_SECOND And MY_SECOND <> MY_THIRD And MY_THIRD <> MY_FOURTH And _
MY_FOURTH <> MY_FIFTH And MY_FIFTH <> MY_SIXTH
MY_FIRST = Int((7 * Rnd) + 1)
MY_SECOND = Int((7 * Rnd) + 1)
MY_THIRD = Int((7 * Rnd) + 1)
MY_FOURTH = Int((7 * Rnd) + 1)
MY_FIFTH = Int((7 * Rnd) + 1)
MY_SIXTH = Int((7 * Rnd) + 1)
Loop
Select Case Range("B4").Value
Case 1
Range("B25:B30").Value = 0
Case 7
Range("B25").Value = 1
Range("B26").Value = 2
Range("B27").Value = 3
Range("B28").Value = 4
Range("B29").Value = 5
Range("B30").Value = 6
Case 2 To 6
n = Range("B4").Value
Dim r, num(6), used(6)
For x = 1 To n: used(x) = 0: Next
For x = 1 To n
1              r = Int(Rnd * 6) + 1
If used(r) = 1 Then GoTo 1
num(x) = r: used(r) = 1
num(6) = 0
Cells(24 + x, 2) = num(x)
Next
End Select
End If
End Sub``````

As for learning VB, I went on an evening collge course to learn it, then getting involved a lot with Excel meant having to hone this knowledge to suit VBA. Helping out on this forum site has taught my an immense amount.

Also, try using the Macro Recorder, this will give a good insight into the code used.

How others have learnt will be different, post this question into the Lounge Forum, you will get many responses.

Thanks so much for your help. I have to write some more code to finish this.

Again...many thanks

I posted this question to the board, but thought you might be able to help me.

I'm trying to do this:

If one of the numbers generated (from our above discussion) and placed in cells B25 to B30 is 1, I want 1 placed in B19; if not, I want 0 placed in B19

If one of the numbers generated and placed in cells B25 to B30 is 2, I want 1 placed in B20; if not, I want 0 placed in B20

I tried this code, but it doesn't work...

If Range("B25:B30") = 1 Then
Cells(19, 2) = 1
Else
Cells(19, 2) = 0
End If

Replies
2
Views
289
Replies
41
Views
1K
Replies
6
Views
328
Replies
0
Views
142
Replies
4
Views
173

1,196,134
Messages
6,013,642
Members
441,777
Latest member

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