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
 

Some videos you may like

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
Joined
Mar 27, 2006
Messages
1,175
Not a VBA solution, but perhaps you can adapt. =RANDBETWEEN(1,450)*2+99
 

artvandelay

New Member
Joined
Jul 31, 2010
Messages
11
Just have the vba code do three separate Odd(randbetween(1,9)) functions then and concatenate them.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
yes! ***Brownie Points to both WestMan & Weaver

and special thanks to everyone else of course!
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
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) so for an even distribution you could use three of these concatenated

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

Watch MrExcel Video

Forum statistics

Threads
1,108,605
Messages
5,523,843
Members
409,539
Latest member
Re1ease

This Week's Hot Topics

Top