Generate Random ODD numbers

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
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

New Member
Joined
Oct 3, 2014
Messages
1
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))
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Try this
This generates 500 numbers consisting of 3 digits, where all the digits are odd.


Code:
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,799
Office Version
  1. 2010
Platform
  1. Windows
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.
Code:
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:
Code:
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
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,816
Office Version
  1. 2019
Platform
  1. Windows
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 :rolleyes:
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,799
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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 :rolleyes:

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.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,816
Office Version
  1. 2019
Platform
  1. Windows
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 :oops:

I think my eyes auto-incorrect things.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,595
Messages
5,523,794
Members
409,534
Latest member
mhearst

This Week's Hot Topics

Top