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)

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)

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 ?

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 ?

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

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

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 ?

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