Generate Random ODD numbers

[neodjandre]

Hello,

Can an expert in this forum devise of a VBA macro that creates 500 random numbers in cells A1:A500?

The numbers must all be odd and contain 3 digits in each cell... i.e.

135
179
977
etc...

many thanks!
andy

 

[Andy2cu]

Column A = ODD(RANDBETWEEN(1,9))*100
Column B = ODD(RANDBETWEEN(1,9))*10
Column C = ODD(RANDBETWEEN(1,9))
Column D = A+B+C

Simple but effective.

Or in One Cell you would write

=(ODD(RANDBETWEEN(1,9))*100)+(ODD(RANDBETWEEN(1,9))*10)+ODD(RANDBETWEEN(1,9))

 

[Special-K99]

Try this
This generates 500 numbers consisting of 3 digits, where all the digits are odd.

Sub k1()
Dim i As Range
For Each i In Range("A1:A20").Cells
l = 0
For j = 1 To 3
k = (Int(Rnd * 5) + 1) * 2 - 1
l = l + k * 10 ^ (j - 1)
Next j
i.Value = l
Next i

End Sub

 

[joeu2004]

Why are we discussing a 4-year-old question?

=(ODD(RANDBETWEEN(1,9))*100)+(ODD(RANDBETWEEN(1,9))*10)+ODD(RANDBETWEEN(1,9))

More simply: select any range, type the following formula, then press ctrl+Enter (not ctrl+shift+Enter and not just Enter):

=--(ODD(RANDBETWEEN(1,9)) & ODD(RANDBETWEEN(1,9)) & ODD(RANDBETWEEN(1,9)))

This generates 500 numbers consisting of 3 digits, where all the digits are odd.

Sub k1()
Dim i As Range
For Each i In Range("A1:A20").Cells
l = 0
For j = 1 To 3
k = (Int(Rnd * 5) + 1) * 2 - 1
l = l + k * 10 ^ (j - 1)
Next j
i.Value = l
Next i
End Sub

Actually, that only generates 20 such numbers.

More simply, select any range, then execute the following:

Sub doit()
Dim c As Range
For Each c In Selection
    c.Value2 = (2 * Int(5 * Rnd) + 1) & (2 * Int(5 * Rnd) + 1) & (2 * Int(5 * Rnd) + 1)
Next
End Sub

 

[jasonb75]

Why are we discussing a 4-year-old question?

I was wondering the same thing

then press ctrl+Enter (not ctrl+shift+Enter and not just Enter):

=--(ODD(RANDBETWEEN(1,9)) & ODD(RANDBETWEEN(1,9)) & ODD(RANDBETWEEN(1,9)))

I'm also wondering why you feel the need to confirm that as an array formula when it contains no arrays

 

[joeu2004]

More simply: select any range, type the following formula, then press ctrl+Enter (not ctrl+shift+Enter and not just Enter):

I'm also wondering why you feel the need to confirm that as an array formula when it contains no arrays

I did not!

I most emphatically wrote "not ctrl+shift+Enter", which is how we array-enter a formula.

Instead, I wrote ctrl+Enter, which "fills the selected cell range with the current entry", as explained in the keyboard shortcut help page.

In other words, it is a shortcut for enter, copy, then select and paste.

 

[jasonb75]

I did not!

I most emphatically wrote "not ctrl+shift+Enter", which is how we array-enter a formula.

Instead, I wrote ctrl+Enter, which "fills the selected cell range with the current entry", as explained in the keyboard shortcut help page.

In other words, it is a shortcut for enter, copy, then select and paste.

My bad

I think my eyes auto-incorrect things.

 

[barry houdini]

Formula-wise both =odd(int(rand()*9)) and Odd(randbetween(1,9)) will have a bias (the first gives you less 9s and the second gives you less 1s)

As per my earlier comment (four years ago), using ODD(RANDBETWEEN(1,9)) wouldn't be a good method if you want a consistent distribution, perhaps better as

=ODD(RANDBETWEEN(0,9))