Hamilton asks how to generate a column of bingo numbers, one at a time. In Episode 733, a tiny macro will add random numbers down column A.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to MrExcel netcast.
I'm Bill Jelen.
Today's question send in by Hamilton is about bingo.
Yesterday's Podcast we had baseball.
Now, we have bingo Hamilton says he wants to generate bingo numbers down column A and he's figured out how to do that using the RAND function.
Well, this formula =RAND() is going to generate a random number between 0 and 1.
Simple enough and so, I'm guessing what Hamilton has done is he's used the Integer of random times 90 plus one that will generate random numbers between 1 and 90 every time.
Now, if you have the Analysis Toolpak turned on, that's under Tools, Add-ins or if you're in Excel 2007, you could just use equal RANDBETWEEN to do the exact same thing RANDBETWEEN 1 comma 90, will generate a random Integer between 1 and 90 very easy to use, but here's Hamilton's problem he says, every time I generate a random number in column A and I want to go to the next cell all of the previous numbers change.
And so, what we're going to do is we're going to set up a tiny little of Macro here that will basically, find the next cell put a random number in and then convert the random number 2 of value.
So, to do this we'll hit Alt+F11 to switch over to VBA will choose Insert, Module just create a little routine here, Sub Draw Number().
First thing I wanna do is I want to find the next blank cell.
So, I'm going to start at the bottom the worksheet Cells (Rows.Count,1), press the End key, and once I press the End key and the up arrow, I'm going to go to the last cell that has a value, I now want to go down one cell.
So, we'll use the OFFSET function to go down one row over 0 columns and we'll select that cell.
Now, once we've selected that cell, Selection.Formula = "=Randbetween(1,90)".
Okay well, this will basically keep adding new random numbers down column A but what we have to do is once that random number has been entered is converted to a value.
So, selection.value = selection.value just a very easy way to do paste special values.
So, our Macro here is called Draw Number.
If I switch back to Excel, and we go to Alt+F8, I choose Draw Number and click options and assign this to a shortcut key maybe control+D and then now we can just hit control+D It should give us a new number, a new number, a new number, a new number, a new number, right on down the line.
Of course you see that number up there in cell A1 is still changing every time.
We really should have started with a completely blank spreadsheet and control D, control D, control D, control D, control D.
There you have it.
Very simple way to create bingo numbers.
Of course now, the problem with this is we still might have duplicates and it would take a very long podcast to go through and figure out how to get rid of the duplicates although certainly possible, I'm guessing Hamilton's just going to deal with the duplicates and draw a new number one happens to pop up.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today's question send in by Hamilton is about bingo.
Yesterday's Podcast we had baseball.
Now, we have bingo Hamilton says he wants to generate bingo numbers down column A and he's figured out how to do that using the RAND function.
Well, this formula =RAND() is going to generate a random number between 0 and 1.
Simple enough and so, I'm guessing what Hamilton has done is he's used the Integer of random times 90 plus one that will generate random numbers between 1 and 90 every time.
Now, if you have the Analysis Toolpak turned on, that's under Tools, Add-ins or if you're in Excel 2007, you could just use equal RANDBETWEEN to do the exact same thing RANDBETWEEN 1 comma 90, will generate a random Integer between 1 and 90 very easy to use, but here's Hamilton's problem he says, every time I generate a random number in column A and I want to go to the next cell all of the previous numbers change.
And so, what we're going to do is we're going to set up a tiny little of Macro here that will basically, find the next cell put a random number in and then convert the random number 2 of value.
So, to do this we'll hit Alt+F11 to switch over to VBA will choose Insert, Module just create a little routine here, Sub Draw Number().
First thing I wanna do is I want to find the next blank cell.
So, I'm going to start at the bottom the worksheet Cells (Rows.Count,1), press the End key, and once I press the End key and the up arrow, I'm going to go to the last cell that has a value, I now want to go down one cell.
So, we'll use the OFFSET function to go down one row over 0 columns and we'll select that cell.
Now, once we've selected that cell, Selection.Formula = "=Randbetween(1,90)".
Okay well, this will basically keep adding new random numbers down column A but what we have to do is once that random number has been entered is converted to a value.
So, selection.value = selection.value just a very easy way to do paste special values.
So, our Macro here is called Draw Number.
If I switch back to Excel, and we go to Alt+F8, I choose Draw Number and click options and assign this to a shortcut key maybe control+D and then now we can just hit control+D It should give us a new number, a new number, a new number, a new number, a new number, right on down the line.
Of course you see that number up there in cell A1 is still changing every time.
We really should have started with a completely blank spreadsheet and control D, control D, control D, control D, control D.
There you have it.
Very simple way to create bingo numbers.
Of course now, the problem with this is we still might have duplicates and it would take a very long podcast to go through and figure out how to get rid of the duplicates although certainly possible, I'm guessing Hamilton's just going to deal with the duplicates and draw a new number one happens to pop up.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
