# 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

#### neodjandre

##### Well-known Member
dave - you got me ! finally i am not that crazy !

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### West Man

##### Well-known Member
Not a VBA solution, but perhaps you can adapt. =RANDBETWEEN(1,450)*2+99

#### artvandelay

##### New Member
Just have the vba code do three separate Odd(randbetween(1,9)) functions then and concatenate them.

#### neodjandre

##### Well-known Member
yeah not very neat but I did it ... thanks anyway for your help all

#### dave3009

##### Well-known Member

=odd(int(rand()*9))&odd(int(rand()*9))&odd(int(rand()*9))

#### Weaver

##### Well-known Member
Something like:

Code:
``````Sub veryOddRandom()
oddarray = Array(1, 3, 5, 7, 9)
l = 3
For Each cel In Range("A1:C100")
r = ""
For i = 1 To 3
r = r & oddarray(Int(Rnd() * 5))
Next
cel.Value = r * 1
Next
End Sub``````

#### Richard Schollar

##### MrExcel MVP

I thought West Man's solution was very succinct and clever

#### neodjandre

##### Well-known Member
yes! ***Brownie Points to both WestMan & Weaver

and special thanks to everyone else of course!

#### GTO

##### MrExcel MVP
Maybe just me, but I still got some even digits in West Man's.

Overly expensive use of evaluate, but just for kicks...

Rich (BB code):
``````Sub exa3()
Dim a(1 To 500, 1 To 1)
Dim i As Long

For i = 1 To 500
a(i, 1) = CLng(Evaluate("=ODD(TRUNC(RAND()*(9-1)+1))") & _
Evaluate("=ODD(TRUNC(RAND()*(9-1)+1))") & _
Evaluate("=ODD(TRUNC(RAND()*(9-1)+1))"))
Next
Range("A1:A500").Value = a
End Sub``````

Weaver: That's sharp!

Hi Richard

Mark

#### 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) so for an even distribution you could use three of these concatenated

=INT(RAND()*5+1)*2-1

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