A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Random Numbers



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 04:47 AM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default 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  
Old May 16th, 2009, 06:23 AM posted to microsoft.public.excel.misc
Jarek Kujawa[_2_]
external usenet poster
 
Posts: 775
Default 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  
Old May 16th, 2009, 06:26 AM posted to microsoft.public.excel.misc
Jim Cone[_2_]
external usenet poster
 
Posts: 434
Default 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  
Old May 16th, 2009, 06:39 AM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default 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  
Old May 16th, 2009, 06:41 AM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default 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  
Old May 16th, 2009, 06:58 AM posted to microsoft.public.excel.misc
Jarek Kujawa[_2_]
external usenet poster
 
Posts: 775
Default 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  
Old May 16th, 2009, 07:17 AM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default 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  
Old May 16th, 2009, 07:41 AM posted to microsoft.public.excel.misc
Jarek Kujawa[_2_]
external usenet poster
 
Posts: 775
Default 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  
Old May 16th, 2009, 07:55 AM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default 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  
Old May 16th, 2009, 08:33 AM posted to microsoft.public.excel.misc
Jarek Kujawa[_2_]
external usenet poster
 
Posts: 775
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.