Hello UA

It is possible to generate 5 digit random numbers using vba for number 0 to 9. Numbers can be repeated e.g 00000, 11111, etc


Ronald

Ronald,

Do you want five unique one-digit random numbers, or do you want one random number with five digits?

For a five-digit number, try this:

= Format(Int(Rnd(1) * 99999) + 1, "00000")

Dennis

Hello Doctor9

I think my question not clear enough

I wanted to generate all possible 5 digit random numbers using numbers between 0 and 9.

E.g 11111, 12345, 14567,98987, etc..... is possible ?

Ronald

This is not a random number issue. If you want ALL possible combinations then you want all numbers between 0 and 99999. You can do this with a loop.

But the next question is why and what do you want to do with these numbers. That will dictate how to do it.

Ronald,

Doctor9's answer does what you asked.

I'm thinking you want generate 10,000 records rather quickly . . . id so, apply ScottGem's suggestion and loop it.

The next question is, 'How many total results (numbers) do you want?'

Hello

Doctor9 's suggestion created one number

How do i put it in loop?

A Cousin doing A'Level maths ( Combinations and Permutations ) wanted to generate all those numbers in Excel ...

Ronald

Ronald,

At this point it sounds like you want Excel to contain the following data:

00000
00001
00002
00003
00004
00005

etc. all the way up to:

99994
99995
99996
99997
99998
99999

If that's so, you can just do this:



If your cousin is trying to determine the number of permutations, it's 100,000.

Hope this helps,

Dennis

Again, how many total numbers do you want? Where will they be applied? (to a form control, directly to a table, what?)

And, yes, those numbers could just as easily be generated in Excel then imported into Access.

Meanwhile, here's a loop construct . . .



Test in the Immediate Window after saving the function to a Module. In the Immediate Window type:

?myGenRandNum(5)

("5" is total number of values you want randomly generated)

hope this helps

Create a table with one numeric field. (I used Table1)

Add 10 records (0-9)

Create a query based on the table

SELECT [Table1].[MyNumber] & [Table1_1].[MyNumber] & [Table1_2].[MyNumber] & [Table1_3].[MyNumber] & [Table1_4].[MyNumber] AS MyNumber
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3, Table1 AS Table1_4;

Or, if this is a one time shot, Open a blank Excel sheet. Type, starting in Column A 0, 1, 2 in subsequent rows. Highlight the three rows and drag the handle in the lower right corner of the highlighted range all they way down to Row 99999

This should autofill the column with numbers 0 to 99999 (note you have to use Excel 2010 for that since Excel 2007 is limited to 65K rows). There are many ways to create a loop. You've gotten some of them

Hello Doctor9

This is combinations with repetition..

This sub has been close



But for a large selection, it takes time to calculate

Hello 'CyberCow

Tested this and generated 5 numbers.

What i wanted to do is to generate all possible 5 digits combinations using numbers between 0 and 9

Got something close in excel



which generates the numbers in selected cells...l

Ronald

Right - change the 5 to 100 and you'll get a hundred numbers. Change it to 100000 and you'll get a hundred-thousand numbers.

You should really check out: Access VBA Tutorial

You've still not made it clear what you're after as a final result, where you want these numbers to end up or why.

This does not make sense. "all possible 5 digits combinations" means every number between 0 and 99999. So you do not want to use a Randon Number generator as that will produce duplicates and it will take longer to check for dups.

I simple incremental loop like Doctor9 suggested


Or what I suggested using the autofill will give all the combinations. But what possible purpose could there be in that?

Kamulegeya,

Can you provide any more details about what, exactly, you're trying to accomplish?

Dennis

Hello Denis

Some one was solving a math problem... how many five digits number can be generated using numbers 0 to 9 with repetitions allowed.

We wanted to see whether it was possible to generate all those possible numbers using vba in excel

Really no practical purpose ...apart from learning and we have learn a lot from the answers we have received

Ronald

Ronald,

You don't need excel if those are the limitations. As long as repetitions are allowed, you just simple math.

The lowest value is 00000, and the highest value is 99999, so the number of unique values is 100,000. If you can't have leading zeroes, you just go from 10000 to 99999, which is 90,000.

Now, if you want to count the number of unique numbers WITHOUT duplicate digits, it's more of a challenge.

Dennis

Hello Denis
The calculation bit we knew 10^5 but the vba bit that what we wanted

Tried to run your code snipet and code an application defined/object define error at


any suggestions?

Ronald

Kamulegeya,

My bad, there's no row zero. Why are you using Access to do this? Just run the following as an Excel macro:



You can't use Integer for i, because it only goes up to 64K.

Dennis

Got it working ....thank you so much