Populating 2 ranges with different random nums in each cell

[Max]

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)

 

[Per Jessen]

Hi

You have to generate a new number for each cell.

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

Regards,
Per

 

[Max]

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 ?

 

[Per Jessen]

This will only allow unique numbers:

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

Set tRng1 = Range("A14")
Set tRng2 = Range("G1:H5")
For Each cell In Range("A14, 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

 

[Max]

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

 

[Per Jessen]

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

 

[Lars-Åke Aspelin]

Here is a slight simplification of the sub

Sub aaa()
Dim tRng As Range

Set tRng = Union(Range("A14"),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

 

[Max]

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

 

[Max]

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

Max

 

[Lars-Åke Aspelin]

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

 

[Max]

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

Max