# Generate Random ODD numbers

#### neodjandre

##### Well-known Member
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
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))

### 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
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
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
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

##### Well-known Member

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.

Last edited:

#### jasonb75

##### Well-known Member
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.

I think my eyes auto-incorrect things.

#### barry houdini

##### MrExcel MVP
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))

Replies
1
Views
67
Replies
11
Views
126
Replies
6
Views
71
Replies
12
Views
126
Replies
0
Views
38