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
  #11  
Old May 16th, 2009, 12:49 PM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default Random Numbers

Jarek,

Works almost perfectly. Random Numbers are perfect however, when applied, I
lose my Formatting for the "Range" on both the Col and Row. Why would this
happen?

Sorry for my delay in replying, I fell asleep.

Bob



"Jarek Kujawa" wrote:

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 -



  #12  
Old May 16th, 2009, 02:46 PM posted to microsoft.public.excel.misc
Chip Pearson
external usenet poster
 
Posts: 1,343
Default Random Numbers

See http://www.cpearson.com/Excel/randomNumbers.aspx and
http://www.cpearson.com/Excel/ShuffleArray.aspx for code that will do
this, among other things.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 15 May 2009 20:47:01 -0700, 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

  #13  
Old May 16th, 2009, 03:10 PM posted to microsoft.public.excel.misc
Jim Cone[_2_]
external usenet poster
 
Posts: 434
Default Random Numbers

Change .Clear to .ClearContents
--
Jim Cone
Portland, Oregon USA


"robert morris"

wrote in message
Jarek,
Works almost perfectly. Random Numbers are perfect however,
when applied, I lose my Formatting for the "Range" on both the Col and Row.
Why would this happen?
Sorry for my delay in replying, I fell asleep.
Bob

  #14  
Old May 16th, 2009, 03:43 PM posted to microsoft.public.excel.misc
robert morris
external usenet poster
 
Posts: 245
Default Random Numbers

Jim,

That was it! Thanks

Bob



"Jim Cone" wrote:

Change .Clear to .ClearContents
--
Jim Cone
Portland, Oregon USA


"robert morris"

wrote in message
Jarek,
Works almost perfectly. Random Numbers are perfect however,
when applied, I lose my Formatting for the "Range" on both the Col and Row.
Why would this happen?
Sorry for my delay in replying, I fell asleep.
Bob


  #15  
Old May 16th, 2009, 09:55 PM posted to microsoft.public.excel.misc
Jack Sons
external usenet poster
 
Posts: 137
Default Random Numbers

Use this:

Sub FillRand() Dim nums() As Integer Dim maxval As Integer Dim
nrows As Integer, ncols As Integer Dim j As Integer, k As Integer Dim
Ptr As Integer Randomize Set s = Selection maxval = s.Cells.Count
nrows = s.Rows.Count ncols = s.Columns.Count ReDim nums(maxval, 2)
'Fill the initial array For j = 1 To maxval nums(j, 1) = j
nums(j, 2) = Int((Rnd * maxval) + 1) Next j 'Sort the array based on
the random numbers For j = 1 To maxval - 1 Ptr = j For k =
j + 1 To maxval If nums(Ptr, 2) nums(k, 2) Then Ptr = k
Next k If Ptr j Then k = nums(Ptr, 1)
nums(Ptr, 1) = nums(j, 1) nums(j, 1) = k k = nums(Ptr,
2) nums(Ptr, 2) = nums(j, 2) nums(j, 2) = k End
If Next j 'Fill in the cells Ptr = 0 For j = 1 To nrows
For k = 1 To ncols Ptr = Ptr + 1 s.Cells(j, k) =
nums(Ptr, 1) Next k Next jEnd SubThis macro uses a two-dimensional
array (nums) to figure out which numbers to use and the order in which they
should be used. Near the beginning of the macro the array is filled with a
static number (1 through the number of cells) and a random number between 1
and the number of cells. This second number is then used to sort the array.
Once the array is stored, it is a simple matter to place the original
numbers in the cells.

By the way, the reason a two-dimensional array is used is because the Rnd
function that VBA uses to generate random numbers can return duplicate
values. Thus, even through the second dimension of the array can have
duplicates in it, when the array is finally sorted, the first dimension will
not have duplicates.

To use the macro, start by selecting the cells you want to have filled with
sequential values in a random order. When you run the macro, that range is
filled. For instance, if you select ten cells and then run the macro, then
those cells are filled with the numbers 1 through 10, in random order.

Jack Sons


"robert morris" schreef in bericht
...

I need help with a VBA for creating NON-REPEATING random numbers between
0-9
in Col B4:B13 and Row C3:L3

Bob



 




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 10:33 AM.


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