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

[ms_metis]

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

Thanks in advance...

 

[onlyadrafter]

Hello,

How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
    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.

 

[ms_metis]

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

 

[ms_metis]

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

 

[onlyadrafter]

Hello,

Have amended the code with the code you supplied

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
    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

 

[ms_metis]

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

 

[ms_metis]

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)
If Target.Address = "$B$4" Then
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

 

[onlyadrafter]

Hello,

Have added the num(6)=0 line

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
    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.

 

[ms_metis]

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

Again...many thanks

 

[ms_metis]

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