# 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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### prabby25101981

##### Active Member
Enable Analysis Toolpak (Tools -> Add Ins -> Analysis Toolpak) and use this function -

=ODD(RANDBETWEEN(100,999))

#### dave3009

##### Well-known Member
With a macro

Code:
``````Sub genrandodds()
Dim RandNo As Integer
Dim i As Integer
For i = 1 To 500
RandNo = Rnd() * 999
If RandNo < 100 Then
RandNo = RandNo + ((Rnd() * 9) * 100)
If RandNo Mod 2 = 0 Then
RandNo = RandNo + 1
End If
End If
Range("A" & i).Value = RandNo
Next i

End Sub``````

#### GTO

##### MrExcel MVP
Would a formula/worksheet function suffice?

In A1: =ODD(TRUNC(RAND()*(999-100)+100))

And copy down...

#### neodjandre

##### Well-known Member

sorry to disappoint all of the 3 of you but your suggestions give even numbers as well..

SOL 1 SOL 2 SOL 3
796 757 869
720 369 571
693 731 867
730 933 127
192 499 753
773 609 195
293 269 303
949 533 295
819 753 343
219 807 923
184 789 269
237 633 293
925 645 207
257 267 921
256 381 621
714 271 711
664 331 819
772 907 249
758 129 849
308 465 119

Perhaps I didn't explain myself properly... I need all the digits to be odd numbers with a VBA macro solution!

#### jasonb75

##### Well-known Member
Enable Analysis Toolpak (Tools -> Add Ins -> Analysis Toolpak) and use this function -

=ODD(RANDBETWEEN(100,999))

There is no way possible for this formula to produce even numbers, so all you need is to code the entry for your range

Code:
``````Sub test()
With ActiveSheet.Range("A1:A500")
.Formula = "=odd(randbetween(100,999))"
.Value = .Value
End With
End Sub``````

#### neodjandre

##### Well-known Member

jason I need all the digits to be odd numbers...

#### jasonb75

##### Well-known Member
Did you try the code, or even read what I said properly?

There is no way possible for this formula to produce even numbers

That means they will all be odd numbers!!!

I just ran the code from A1:ZZ999

Result ODD numbers 701298
EVEN numbers 0

If it's not working for you then your system is proccessing data incorrectly and is in need of professional repair!

Last edited:

#### dave3009

##### Well-known Member
Do you mean you want all odd numbers

i.e. 989 is not an option because the number 8 is even?

#### neodjandre

##### Well-known Member
ok are the digits in this number "287" - all odd ?

Replies
3
Views
525
Replies
1
Views
80
Replies
7
Views
529
Replies
0
Views
472
Replies
24
Views
948

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,486
Messages
5,831,970
Members
430,099
Latest member
rdhoy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back