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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
Enable Analysis Toolpak (Tools -> Add Ins -> Analysis Toolpak) and use this function -

=ODD(RANDBETWEEN(100,999))
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
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
Joined
Dec 9, 2008
Messages
6,154
Would a formula/worksheet function suffice?

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

And copy down...
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
10,652
Office Version
2019
Platform
Windows
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
Joined
Nov 29, 2006
Messages
926

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,652
Office Version
2019
Platform
Windows
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
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
Do you mean you want all odd numbers

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,386
Messages
5,510,993
Members
408,823
Latest member
TJKnight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top