Populating 2 ranges with different random nums in each cell
 

Trying the line below to populate 2 ranges with random nums between 1-100,
but it returns the same num, albeit random, in every cell. How to
tweak/enhance it so that every cell would be populated with a different
random num (no duplicates)? Thanks

[a1:d4,g1:h5].value = Int((100* Rnd) + 1)

Populating 2 ranges with different random nums in each cell
 

Hi

You have to generate a new number for each cell.

Sub aaa()
For Each cell In Range("A1:D4, G1:h5")
cell.Value = Int((100 * Rnd) + 1)
Next
End Sub

Regards,
Per

On 18 Jan., 09:36, "Max" wrote:
Trying the line below to populate 2 ranges with random nums between 1-100,
but it returns the same num, albeit random, in every cell. How to
tweak/enhance it so that every cell would be populated with a different
random num (no duplicates)? Thanks

[a1:d4,g1:h5].value = Int((100* Rnd) + 1)

Populating 2 ranges with different random nums in each cell
 

Thanks, Per. That does the mechanics well. But I noticed some of the nums
generated could repeat, which is not desired. How to make it so that there
are no duplicates ?

Populating 2 ranges with different random nums in each cell
 

This will only allow unique numbers:

Sub aaa()
Dim tRng1 As Range
Dim tRng2 As Range

Set tRng1 = Range("A1:D4")
Set tRng2 = Range("G1:H5")
For Each cell In Range("A1:D4, G1:h5")
Do
cell.Value = Int((100 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(tRng1, cell.Value) _
+ WorksheetFunction.CountIf(tRng2, cell.Value) = 1
Next
End Sub

Regards,
Per

On 18 Jan., 09:58, "Max" wrote:
Thanks, Per. That does the mechanics well. But I noticed some of the nums
generated could repeat, which is not desired. How to make it so that there
are no duplicates ?

Populating 2 ranges with different random nums in each cell
 

Many thanks, Per. That does it good.

Could you explain how this part of it helps in ensuring uniqueness? Thanks

Loop Until WorksheetFunction.CountIf(tRng1, cell.Value) _
+ WorksheetFunction.CountIf(tRng2, cell.Value) = 1

Max

Populating 2 ranges with different random nums in each cell
 

Hi Max

Thanks for your reply, I'm glad to help.

It's part of a Do ... Loop

The loop will generate a new random value in "cell" until there is
only one instance of the number in the target range(s).

Hopes this help.

Per

On 18 Jan., 11:17, "Max" wrote:
Many thanks, Per. That does it good.

Could you explain how this part of it helps in ensuring uniqueness? Thanks

* *Loop Until WorksheetFunction.CountIf(tRng1, cell.Value) _
* * * *+ WorksheetFunction.CountIf(tRng2, cell.Value) = 1

Max

Populating 2 ranges with different random nums in each cell
 

Here is a slight simplification of the sub

Sub aaa()
Dim tRng As Range

Set tRng = Union(Range("A1:D4"),Range("G1:H5"))
For Each cell In tRng
Do
cell.Value = Int((100 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(tRng, cell.Value) = 1
Next
End Sub

The OP asked how the loop helped in making the values unique
and the answer is that the exit criteria of the loop is that the
number of occurances (CountIf) , in the combined range (tRng), of the
just generated random number (cell.Value) is one.

Hope this helps / Lars-Åke


On Sun, 18 Jan 2009 01:50:00 -0800 (PST), Per Jessen
wrote:

This will only allow unique numbers:

Sub aaa()
Dim tRng1 As Range
Dim tRng2 As Range

Set tRng1 = Range("A1:D4")
Set tRng2 = Range("G1:H5")
For Each cell In Range("A1:D4, G1:h5")
Do
cell.Value = Int((100 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(tRng1, cell.Value) _
+ WorksheetFunction.CountIf(tRng2, cell.Value) = 1
Next
End Sub

Regards,
Per

On 18 Jan., 09:58, "Max" wrote:
Thanks, Per. That does the mechanics well. But I noticed some of the nums
generated could repeat, which is not desired. How to make it so that there
are no duplicates ?

Populating 2 ranges with different random nums in each cell
 

Lars, thanks for the thoughts

I hit an error 1004 when I tried running your sub:
Unable to get countif property of the worksheetFunction class

Debug pointed at this line:
Loop Until WorksheetFunction.CountIf(tRng, cell.Value) = 1

Max

Populating 2 ranges with different random nums in each cell
 

Thanks for the clarification, Per.
I think I got it.

Max

Populating 2 ranges with different random nums in each cell
 

On Sun, 18 Jan 2009 20:55:42 +0800, "Max"
wrote:

Lars, thanks for the thoughts

I hit an error 1004 when I tried running your sub:
Unable to get countif property of the worksheetFunction class

Debug pointed at this line:
Loop Until WorksheetFunction.CountIf(tRng, cell.Value) = 1

Max


It seems CountIF doesn't like noncontiguous ranges, so my
simplification doesn't work.

/ Lars-Åke

Populating 2 ranges with different random nums in each cell
 

Thanks for the clarification, Lars
No prob, the task can be done with Per's sub

Max