Random whole number between 100000 - 999999

[Sc0tt1e]

Can someone explain how to get this to work please, I have been reading for an hour and there are many different syntaxs and the one most commonly used doesn't work when I try to run a macro?

Please Login or Register  to view this content.

[jayesh25]

Your function seems fine. an Integer only holds 32 bits hence it's unable to a hold a value that high.

try this

Please Login or Register  to view this content.

[daffodil11]

There's an instrinsic function for whole numbers.

Please Login or Register  to view this content.

[joeu2004]

Can someone explain how to get this to work please,
[....]

Please Login or Register  to view this content.

Several mistakes:

1. Use type Long instead of type Integer. Type Integer cannot handle values larger than 32767.

2. Change the random expression to Int((999999 - 100000) * Rnd) + 100000. Your expression returns random values between 1 and 900001, not between 100000 and 999999 as intended.

3. Add the following code in the beginning if you do not have similar code somewhere else.

Please Login or Register  to view this content.

That ensures that Rnd returns a difference sequence of random values each time.

Generally, I agree with the suggestion to use WorksheetFunction.RandBetween(100000, 999999) instead. It is simpler.

But WorksheetFunction.RandBetween is slower. On my computer, it is 20 times slower. YMMV. On the other hand, we are still talking about microseconds. Nothing to worry about unless your procedure is called many thousands of times.