How to Make a Lucky Draw in an Excel Worksheet via VBA

In Excel worksheet, you can also make a lucky draw. In this writing piece, we are going to show you how to finish this task in Excel.

A Lucky draw is very common in our daily life. For example, you need to give bonuses to employees in the end of a year. You would like to select some lucky employees randomly. Therefore, you can use the Excel file to make a lucky draw.

Make a Lucky Draw

In this part, we will introduce the methods in detail.

1. Open the Excel file with all the information of the employees. In this example, there are 18 employees in the worksheet. And the existing bonuses are calculated according to their sales volume.
2. And then press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
3. After that, insert a new module into the editor.
4. In this step, copy the following codes into the new module:

Sub MakeLuckyDraw()
  Dim nNumber As Integer, nRowIndex As Integer

  'Generate a random number
  nNumber = Int(Rnd() * (19 - 2 + 1)) + 2

  'Go through the list
  For nRowIndex = 2 To 19
    If nRowIndex = nNumber Then
      MsgBox "The lucky one is " & Cells(nRowIndex, 1).Value
    End If
  Next nRowIndex
End Sub

In this codes, the formula “Int(Rnd() * (19 – 2 + 1)) + 2” will make sure that the random number will be in 2-19. You can also modify the formula according to your need.

Actually, except for the above formula to generate a number, you can also use the worksheet function “RandBetween”. And you need to change the codes in the module.

'Generate a random number
nNumber = Application.WorksheetFunction.RandBetween(2, 19)

5. After that, click the tab “Developer” in the ribbon.
6. And then click the button “Insert” in the toolbar.
7. In the drop-down list, choose the option “Button”.
8. After that, click in the worksheet. You will see the “Assign Macro” window.
9. In this window, click the macro that you have input into the new module.
10. And then click the button “OK”.
11. Next you can see the button in the worksheet. Right click the button.
12. In the pop-up window, choose the option “Edit Text”.
13. Now give a name for the button. We will name it “Lucky Person”.
14. You have finished all the setting. And you can start the activity. Click the button.
15. And then you will see a window pop-up with a name of your employee.

From the above steps, you can see that using Excel to make a lucky draw is very convenient. The next time you can also consider using Excel files for those tasks.

How to Repair Your Excel Files

The majority of the users will exit the Excel application as long as they meet with errors in Excel. And the next time when they open an Excel file, the problems will no longer exist. But if you meet with severe Excel problems, you need to take additional actions. You can use a third-party tool to repair damaged Excel xlsx and fix Excel errors. This is also the most effective method to deal with Excel errors.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupt doc data and outlook repair software products. For more information visit www.datanumen.com