• 100% Satisfaction Guarantee

R.R. Jha, Tutor
Category: Homework
Satisfied Customers: 5082
Experience:  B.Tech
R.R. Jha is online now

### Resolved Question:

task of assigning random numbers to the squares, and selecting the winning square.

Whenever the user clicks the "Go" button, your VBA program should assign new random numbers to the cells in the worksheet and also randomly select the winning cell and change its background color to red (or some other color). No formulas should be placed in any cells on the worksheet.

Requirements & Hints:
1) You may use formulas to create the row and column headings in the table. However, there should not be any other formulas in the worksheet. All the random numbers should be generated in VBA and placed in the cells as numeric constants (not cell formulas).

2) In VBA, numbers between 1 and 500 can be randomly generated via: 1 + Int(Rnd * 500))

3) You will need to figure out how to make VBA loop through the 500 cells in the range containing the numbers. While the lectures in Unit 5 describe the Cells( ) collection of a range object as requiring two arguments to specify a row and column (like Cells(r, c)), you can also use a single argument (like Cells(n)) to refer to the nth cell in a range.

4) There are a variety of ways to approach this problem. One way is to first fill the range
sequentially with the integers from 1 to 500. Then, you could go to each cell (one at a time), generate a random cell number between 1 and 500 and then swap the value in the current cell with the value that is in whatever cell number you generate randomly. (See the next page for a detailed walk-through of this procedure using a simplified example.).

5) Make sure the macro you create is Public (not Private). Putting the “Go” button in your application Once you have your VBA code working, you need to add a “Go” button to your spreadsheet that the user can click to execute your code. I strongly recommend that you get your code working first and then follow the instructions below to create a Go button and connect your code to it. To create the “Go” button first make sure you have the Developer tab available on the Ribbon in Excel. If you don’t have a Developer tab do one of the following: In Office 2007 click the Office button, Excel Options, Popular, and click the check box for “Show Developer tab in the Ribbon.”

In Office 2010 & 2013 click File, Options, Customize Ribbon, and check the box next to Developer in the right-hand pane. Now, click Developer, Insert, and click the “Button” icon beneath Form Controls, and then click and drag to draw a button control on your spreadsheet. (Note that there is another button icon under ActiveX Controls, but I strongly recommend using the one under Form Controls for this exercise.) A window
should appear allowing you to assign a (Public) macro to this button. Select your macro and click OK. If you click your button, it should now run the macro you assigned to it.
If you right-click your button you can edit the text that appears on the button (among other things).

Simplified Example

Suppose I have a range filled with numbers from 1 to 15...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

First, note that while this range appears to have 3 rows and 5 columns, you can also view it as a list (or collection) of 15 cells (see below) that just happens to be displayed on three rows. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Now suppose I want to scramble these numbers in random fashion.
I start with cell #1 (that also happens to currently contain the number 1).
I then randomly pick a cell to switch values with cell #1.
So I generate an integer value for R from 1 to 15.
Let's say it is 13.
Note that R=13 refers to the 13th cell (which happens to currently (above) contain the value 13).
Now swap the values in cell #1 and cell #13.
The result is as follows: 13 2 3 4 5 6 7 8 9 10 11 12 1 14 15
OK, now move on to cell #2.

I randomly pick a cell to switch values with cell #2.
So I generate an integer value for R from 1 to 15
Let's say it is 5.
Note that R=5 refers to the 5th cell (which happens to currently (above) contain the value 5).
Now swap the values in cell #2 and cell #5.
The result is as follows: 13 5 3 4 2 6 7 8 9 10 11 12 1 14 15
OK, now move on to cell #3.

I randomly pick a cell to switch values with cell #3.
So I generate an integer value for R from 1 to 15
Let's say it is 13.
Note that R=13 refers to the 13th cell (which happens to currently (above) contain the value 1).
Now swap the values in cell #3 and cell #13.
The result is as follows: 13 5 1 4 2 6 7 8 9 10 11 12 3 14 15

If you continue this process for cell #4, 5, 6, ..., 15 you get a scrambled version of the original numbers without any
repeats.
Submitted: 2 months ago.
Category: Homework
Expert:  R.R. Jha replied 2 months ago.
Hi,

Thanks
Customer: replied 2 months ago.

Will do, I have to leave for work but I will send it to you soon.

Thanks again

Customer: replied 2 months ago.

Attachment: 2013-09-26_105508_hw3.pdf

Attachment: 2013-09-26_105755_3do0357.xlsx

I could only upload the excel sheet with the .xlsx extension but it has to saved as a .xlsm extension.

Thanks Dan

Expert:  R.R. Jha replied 2 months ago.
Received the files. I'll review them and let you know.
Customer: replied 2 months ago.

Thanks, XXXXX XXXXX is greatly appreciated.

Expert:  R.R. Jha replied 2 months ago.
Most welcome!
Expert:  R.R. Jha replied 2 months ago.
You can view this answer by clicking here to Register or Login and paying \$3.
R.R. Jha, Tutor
Category: Homework
Satisfied Customers: 5082
Experience: B.Tech
Customer: replied 2 months ago.

Thanks again, I may call on you in the future.

Expert:  R.R. Jha replied 2 months ago.
You're most welcome! You may send your questions directly to me by adding 'For RRJha' at the start of new question.

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
• He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX

• ### Manal Elkhoshkhany

#### Satisfied Customers:

4514
More than 5000 online tutoring sessions.
< Last | Next >

### Manal Elkhoshkhany

#### Satisfied Customers:

4514

More than 5000 online tutoring sessions.

### Linda_us

#### Satisfied Customers:

3099

Post Graduate Diploma in Management (MBA)

### Lani S.

#### Satisfied Customers:

2453

Registered Nurse, Internet Researcher, Private Tutor

### Chris M.

#### Satisfied Customers:

2258

Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)

### LogicPro

#### Satisfied Customers:

2193

Expert in Java C++ C C# VB Javascript Design SQL HTML

### F. Naz

#### Satisfied Customers:

1962

Experience with chartered accountancy