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

 

[neodjandre]

dave - you got me ! finally i am not that crazy !

 

[West Man]

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

 

[artvandelay]

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

 

[neodjandre]

yeah not very neat but I did it ... thanks anyway for your help all

 

[dave3009]

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

 

[Weaver]

Something like:

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]

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

 

[neodjandre]

yes! ***Brownie Points to both WestMan & Weaver

and special thanks to everyone else of course!

 

[GTO]

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

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

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]

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