Last week I released the latest and greatest version of my Super Bowl squares game template with more ways to play than before. Even if you don’t care for football or sports, numerous lessons can be learned by dissecting this spreadsheet, including:
Sub Clear_Random_Numbers()
'clear the random numbers
Dim DelRange As Range
Set DelRange = Range("B5:B14")
DelRange.ClearContents
End Sub
Unlike a formula, if you insert a row or column and your target range moves, the macro will not update automatically like a formula will. Keep this in mind when making changes to a spreadsheet containing a macro. It’s a good idea to have the layout and format fixed as much as possible before writing your macro code.
To generate random numbers in a range with a macro, modify the code below. This code generates random numbers between 0 and 9. To start from 1, take out the “-1”. To get more random numbers, change 10 to 100 or whatever value you need.
Sub Generate_Random_Numbers()
'define range of cells for NFC random numbers
Dim NFCRange As Range
Set NFCRange = Range("B5:B14")
NFCRange.ClearContents
For Each c In NFCRange
Do
c.Value = (Int((10 * Rnd + 1))) - 1
Loop Until WorksheetFunction.CountIf(NFCRange, c.Value) < 2
Next
The next macro for the Super Bowl squares sheet is for the Assign Names Randomly sheet. This style of play allows users to enter their name then input how many squares they want. So we need to generate the complete list of names. The key to this program is getting the row numbers to add correctly.
Nick 3
Sarah 2
Henry 1
List would look like this:
Nick
Nick
Nick
Sarah
Sarah
Henry
How do we get the result? Use loops to repeat the code that adds the name to the list.
Sub Assign_Random_Number_For_Names()
'stop screen updating so macro runs faster
Application.ScreenUpdating = False
'clear the range
Dim DelRange As Range
Set DelRange = Range("AA2:AA101")
DelRange.ClearContents
'generate a list of all the names in order to populate the field
Dim row As Integer
Dim NumberSquares As Integer
Dim Squares As Integer
Dim RowNum As Integer
Dim PlayerName As String
RowNum = 2
For row = 2 To 101
NumberSquares = Excel.Cells(row, 24)
For Squares = 1 To NumberSquares
PlayerName = Excel.Cells(row, 23)
Excel.Cells(RowNum, 27) = PlayerName
RowNum = RowNum + 1
Next
Next
In this spreadsheet you may have also noticed I used a dynamic list – a drop down list where the values change based on other cell’s value. There are only one hundred squares in Super Bowl squares, so when each player picks how many squares they want, the total value can only add to one hundred. When making your selection it should only show how many squares remain, basically it needs to subtract from 100 the total value of squares already entered. This is done in column P using an IF and ROW formula. If the total value of squares taken is less than one hundred, then show the row number, if not, show nothing.
I hope by examining this template you’ve learned a thing or two about Excel.
- How to assign a macro to a command button
- How to clear cells with a macro
- How to generate random numbers in Excel with a macro
- How to use a macro to create a list based on cell values
- How to create a dynamic drop down list
1. How to assign a macro to a command button
To insert a command button into your Excel spreadsheet you’ll want the Developer tab to be available. Go to File > Options> Customize Ribbon and under Main Tabs check Developer. Now at the top of Excel you should see the Developer tab. Click on the Insert button drop drown, then under form controls click Command Button. Place the button wherever you want along with the desired size. To have a macro run on the click of the button, right click the command button and select Assign macro. Pick your macro and you’re good to go!
2. How to Clear a Range with an Excel Macro
To clear a range with an Excel macro, use the code below. When you click the “Clear Numbers” button, it is linked to this macro and will clear the range.Sub Clear_Random_Numbers()
'clear the random numbers
Dim DelRange As Range
Set DelRange = Range("B5:B14")
DelRange.ClearContents
End Sub
Unlike a formula, if you insert a row or column and your target range moves, the macro will not update automatically like a formula will. Keep this in mind when making changes to a spreadsheet containing a macro. It’s a good idea to have the layout and format fixed as much as possible before writing your macro code.
3. How to generate random numbers in Excel with a macro
To generate random numbers in a range with a macro, modify the code below. This code generates random numbers between 0 and 9. To start from 1, take out the “-1”. To get more random numbers, change 10 to 100 or whatever value you need.
Sub Generate_Random_Numbers()
'define range of cells for NFC random numbers
Dim NFCRange As Range
Set NFCRange = Range("B5:B14")
NFCRange.ClearContents
For Each c In NFCRange
Do
c.Value = (Int((10 * Rnd + 1))) - 1
Loop Until WorksheetFunction.CountIf(NFCRange, c.Value) < 2
Next
4. Macro to Create a List Based on Cell Values
The next macro for the Super Bowl squares sheet is for the Assign Names Randomly sheet. This style of play allows users to enter their name then input how many squares they want. So we need to generate the complete list of names. The key to this program is getting the row numbers to add correctly.
Nick 3
Sarah 2
Henry 1
List would look like this:
Nick
Nick
Nick
Sarah
Sarah
Henry
How do we get the result? Use loops to repeat the code that adds the name to the list.
Sub Assign_Random_Number_For_Names()
'stop screen updating so macro runs faster
Application.ScreenUpdating = False
'clear the range
Dim DelRange As Range
Set DelRange = Range("AA2:AA101")
DelRange.ClearContents
'generate a list of all the names in order to populate the field
Dim row As Integer
Dim NumberSquares As Integer
Dim Squares As Integer
Dim RowNum As Integer
Dim PlayerName As String
RowNum = 2
For row = 2 To 101
NumberSquares = Excel.Cells(row, 24)
For Squares = 1 To NumberSquares
PlayerName = Excel.Cells(row, 23)
Excel.Cells(RowNum, 27) = PlayerName
RowNum = RowNum + 1
Next
Next
5. How to create a dynamic drop down list
In this spreadsheet you may have also noticed I used a dynamic list – a drop down list where the values change based on other cell’s value. There are only one hundred squares in Super Bowl squares, so when each player picks how many squares they want, the total value can only add to one hundred. When making your selection it should only show how many squares remain, basically it needs to subtract from 100 the total value of squares already entered. This is done in column P using an IF and ROW formula. If the total value of squares taken is less than one hundred, then show the row number, if not, show nothing.
I hope by examining this template you’ve learned a thing or two about Excel.
