If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Random Numbers
I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#2
|
|||
|
|||
Random Numbers
would this macro help?
Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#3
|
|||
|
|||
Random Numbers
Bob,
I think your requirements are a little too strict. g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#4
|
|||
|
|||
Random Numbers
Jim,
I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#5
|
|||
|
|||
Random Numbers
Jarek,
Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob |
#6
|
|||
|
|||
Random Numbers
having re-read the conditions I have to agree with Jim
they are to strict are you trying to populate C3:L3 AND B4:B13 with random integers ranging from 0 to 9? if so I cannot provide a solution or you meant to populate both ranges SEPARATELY? On 16 Maj, 07:41, robert morris wrote: Jarek, Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear Â* Â* For Each cell In .Cells repeat: Â* Â* k = WorksheetFunction.RandBetween(0, 9) Â* Â* Â* Â* If WorksheetFunction.CountIf(.Cells, k) = 0 Then Â* Â* Â* Â* Â* Â* cell = k Â* Â* Â* Â* Else Â* Â* Â* Â* Â* Â* GoTo repeat Â* Â* Â* Â* End If Â* Â* Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#7
|
|||
|
|||
Random Numbers
Jarek,
Yes, populate both ranges separately. Possibly two different Codes? I appreciate your help. Bob "Jarek Kujawa" wrote: having re-read the conditions I have to agree with Jim they are to strict are you trying to populate C3:L3 AND B4:B13 with random integers ranging from 0 to 9? if so I cannot provide a solution or you meant to populate both ranges SEPARATELY? On 16 Maj, 07:41, robert morris wrote: Jarek, Thanks, your Code works perfectly for the Column B but does not address Row 3. Bob "Jarek Kujawa" wrote: would this macro help? Sub cus() Dim cell As Range With Range("B4:B13") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub On 16 Maj, 05:47, robert morris wrote: I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#8
|
|||
|
|||
Random Numbers
i.e. for row 3 from 0 to 9 and for col B from 0 to 9?
On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon Â*USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#9
|
|||
|
|||
Random Numbers
Jarek,
Yes, random numbers between 0-9, Col Range B4:B13 and random numbers between 0-9, Row Range C3:L3. As I said, could be two codes with Buttons. Bob "Jarek Kujawa" wrote: i.e. for row 3 from 0 to 9 and for col B from 0 to 9? On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#10
|
|||
|
|||
Random Numbers
for C3:L3
Sub cus2() Dim cell As Range With Range("C3:L3") ..Clear For Each cell In .Cells repeat: k = WorksheetFunction.RandBetween(0, 9) If WorksheetFunction.CountIf(.Cells, k) = 0 Then cell = k Else GoTo repeat End If Next End With End Sub then assign cus to Button1 and cus2 to Button2 is this ok? On 16 Maj, 08:55, robert morris wrote: Jarek, Yes, random numbers between 0-9, Col Range B4:B13 and random numbers between 0-9, Row Range C3:L3. As I said, could be two codes with Buttons. Bob "Jarek Kujawa" wrote: i.e. for row 3 from 0 to 9 and for col B from 0 to 9? On 16 Maj, 07:39, robert morris wrote: Jim, I need random numbers for BOTH Row 3 and Col B. Have I answered your question correctly? Bob "Jim Cone" wrote: Bob, I think your requirements are a little too strict. g You only allow ten unique numbers (0 to 9) and ten cells in each range. Are you just trying to random sort all ten numbers? -- Jim Cone Portland, Oregon Â*USA "robert morris" wrote in message I need help with a VBA for creating NON-REPEATING random numbers between 0-9 in Col B4:B13 and Row C3:L3 Bob- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
Thread Tools | |
Display Modes | |
|
|