Generating a Random Number Table, where all of the numbers within the table are not used

[robreich]

I received some help on this Forum last October from Rick Rothstein creating a Random Number generator table, assuming 500 numbers (1 - 500). This Table is being used for a Reverse Raffle at a Charity Function in Ohio. To execute the macro, I am prompted to enter the first number in the Table, the last number in the Table, and how many numbers I want in each row of the Random Number Table. A question has come up as we approach our first use of the macro this coming October. What if all of the 500 tickets are not sold? They are being sold by multiple people that are members of the organization that is sponsoring the Charity Function. Everyone is given 10 tickets to sell. In a lot of cases, they sell only a portion of them and turn the unsold ones back into the Chairman. He then tries to use them as he gets more requests for tickets, but by the Evening of the fund raiser, there are always a number of tickets that have not been sold. Obviously, if the last ticket sold is less than 500, we simply enter that as the LAST NUMBER. However, is there a way to deal with the issue if tickets within the Table have not been sold, i.e. Tickets Number 275 thru 280 do not sell, 340 thru 345 do not sell, etc.? Right now, we are assuming that their are no unsold tickets in the Table of Numbers. One of the reasons of trying to address this is that every 10th ticket drawn wins a $25.00 Gift Card. If we simply put the unsold tickets up on the board and remove them as they are "drawn", then the "unsold tickets" mess up the Gift Cards.


I was thinking along the lines of building a Table Range that contains all of the SOLD Tickets only (leaving a blank cell where a ticket has not sold). Then, having the macro pull the random numbers from this Table Range. Not sure if that makes sense or it is doable, but if someone could help me with this, it would be greatly appreciated.

I am working in EXCEL 365 and my Operation System is Windows 10.

If someone needs additional information or has questions, please post them here. I will be checking the forum daily.

Thank you in advance for any help you can give me.

Bob Reichert

 

[robreich]

Peter,

I used your Program at our Charity Event to do a Reverse Raffle where we had 530 numbers up on a Peg Board and we removed them 1 at a time until we had four numbers left and they were the money winners. The Program worked flawlessly and we all were very happy in that it cut about an hour out of the time it took last year to manually draw 465 of the numbers out a hopper and remove them from the board.

I would like to know if you can make one minor change for me. I know I asked that all of the existing data be deleted from Sheet1 and Sheet2 "at the very beginning of the run". However, right now, as soon as I open up the file, the StartOver Macro runs automatically and erases everything and prompts me to enter the data in the Table on Sheet1. Now that I have used it in an actual situation, I would rather be able to open the file and "Run" the StartOver Macro myself and then the DrawNumbers Macro when ready. Last night, after we were done, I closed and saved the file. Then one of the Sponsors of the Event came up and wanted to see the Table of Random Numbers that was generated because he couldn't believe how smoothly the Program ran and how fast we finished. Of course, when I opened the file, I couldn't get past the prompt to fill in the Table on Sheet1 and then all of the Table was erased. I was able to show him how the Program worked by creating a new Table very quickly, but letting him know that the Table just created was entirely different than the one we created during the event. He still was very happy.

With the above change, I would be able to open the file, see all of the data from the last run, and then StartOver, deleting everything and then running a new Table.

Thanks,

Bob

 

[Peter_SSs]

Two options below. I'd probably go for the second one, but up to you.

A.
Just remove this code from the ThisWorkbook module

In the vba window, double-click the ThisWorkbook module of your project and paste this code.

Private Sub Workbook_Open()
  StartOver
End Sub

B.
Replace the above code with this. When you open the workbook you get the option to clear the existing data or not.

Private Sub Workbook_Open()
  If MsgBox(Prompt:="Clear all existing data?", Buttons:=vbYesNo) = vbYes Then StartOver
End Sub

 

[robreich]

OK, Peter. I am lost. I don't remember how I accessed the ThisWorkbook Module. When I click on the Vba Button, I don't find a ThisWorkbook​ module to double-click on. I am working in EXCEL 365

 

[Rick Rothstein]

OK, Peter. I am lost. I don't remember how I accessed the ThisWorkbook Module. When I click on the Vba Button, I don't find a ThisWorkbook​ module to double-click on. I am working in EXCEL 365

In the VB editor... look for the Project-VBAProject window, locate the item labeled "ThisWorkbook" and double-click it.

 

[robreich]

I am not familiar with the VBA Section of EXCEL at all, so I simply started all over with a New Workbook. I copied Peter's latest Code into the Macro Section, leaving the Code that he wanted pasted into the VBA section completely out. Program works the way I want it to. I tested it several times and it works fine.

Rick - I made one change to your latest code( .NumberFormat = "@" ) , changing the "@" to "000"). This gives me the numbers in a 3 digit format (001, 010, etc). I now have two versions that I can use to generate my Random Number Table, both of which I like.

Thanks for your help, Guys.

 

[Peter_SSs]

I am not familiar with the VBA Section of EXCEL at all, so I simply started all over with a New Workbook. I copied Peter's latest Code into the Macro Section, leaving the Code that he wanted pasted into the VBA section completely out. Program works the way I want it to. I tested it several times and it works fine.

That's good, but it would be good to try to get the code where I intended and the descriptions "Macro Section" and "VBA section" are not clear to me. Nor am I sure exactly what you are referring to as "Peter's latest Code".

So, perhaps you could make a copy of your latest workbook and try these steps?
With your workbook open at Sheet1 or Sheet2

1. ALT+F11 to bring up the VBA window.

2. In the VBA window, use the menu bar at the top to: View - Project Explorer (just to ensure that part of the VBA window is visible)

3. In the Project Explorer pane (usually at the left side of the window and labelled "Project - VBAProject") you should see one or more items labelled VBA Project (????) where ???? is the name of the file. One of these projects will have your file name where I have ????

4. Your project will have other sub-items showing below it or else a "+" to the left of the name. If "+", click that to expand the display. Then, as sub-items to your project you should see at least "Microsoft Excel Objects" and "Modules". If those two items are not already expanded, click the "+" at the left of each.

5. Now, In the Microsoft Excel Objects section you should see ThisWorkbook at the bottom of that section. Double-click on the text Thisworkbook and at the right is where the code from section B of post 32 should get pasted.

6. In the Modules section, it sounds like you already have a Module (probably called "Module1") and if you double click that item then on the right is where you should see (or put) all the code from the second Code window in post 25. Don't forget that single line right at the top of that code window in post 25 and also to include the extra blue line from post 27.

7. Save and close the workbook then re-open it.

 

[robreich]

Thank you, Peter. I printed your instruction in Post Number 36 out in WORD and then was able to follow them and find everything and get your recommended Change B copied from Post 32 into the correct place. I then went into Module 1 and found your Code from Post 25. I edited it to include your change from Post 27, closed everything, and saved my project. When I re-opened it, it works as you indicated in Post 27. I selected NO, looked at everything that I had previously saved, then ran StartOver and then DrawNumbers and it all worked as before.

Thanks, again. Your notes helped me discover that the VBA window is not as intimidating as I thought. Since I was not sure what I was doing before, I was afraid to go too far because I was concerned about doing something that would mess the whole thing up. I didn't realize that the "+" sign would open up the contents of those Line Items before.

Bob

 

[Peter_SSs]

Cheers. Glad you could at least see how that worked. If you don't run another draw until, say, next week then when you open the file next week you can safely answer 'Yes' to that question and be ready to go with the data for the new draw.

 

[robreich]

Peter,
I had one question that I forgot to ask. I have noticed that when the STARTOVER Sub is run, the format of the numbers in Column D on Sheet1 is changed to a TEXT format. In the Macro, I see a line of Code that reads .Columns("D").NumberFormat = "@". I assume that this is where the format of Column D is changed. Is this critical to be in this format for some function in the Macros? I would prefer that this column remain in a General format so that if I have a bunch of numbers, I can type a formula below the last one just to count the number of numbers entered just to check that I have them all entered. If they are in TEXT format, I am not sure that they will be counted using the EXCEL =COUNT formula.

 

[Peter_SSs]

Column D in Sheet1 is to record numbers of tickets not sold. It works so that if there is a range of numbers not sold you can enter them like this 2-9
If that column is formatted as general, and you enter 2-9 into a cell it will turn into a date (at least it does for me). To keep it as 2-9 the column needs to be formatted as Text.
If you always enter numbers individually, then the format doesn't need to be Text. However if 5-76 were not sold, that is a lot of single numbers to enter.
If you want to keep the ability to enter groups of numbers like 34-39 then there would be a couple of choices ..
a) Keep the Text formatting in column D and put your formula in another column (eg cell E1), or
b) Make column D General formatting (& edit the code so it doesn't re-apply the Text formatting) and if you wanted to enter a group that might turn into a date, you would have to remember to prefix the entry with an apostrophe. So you would enter
'2-4