Randomly Select Cells

  • Thread starter Thread starter Debbie
  • Start date Start date
D

Debbie

Hi All,

I'm using XL97 on Win98, and I'm in the process of
creating a test blank. (See example below.)
What I'd like is to be able to run some code to
randomly select one circle in each set of answers
and color that circle red, so that I know to place
the correct answer next to the letter that corresponds
with the red circle.

There will be 120 questions. I gave names to a few of
the ranges, such as Q1Area, Q2Area, Q3Area, and then
I tried using Rand, but I have had no luck. I really
do not want to have to name 120 ranges, but I will,
if necessary.

The question number is in column A, and the question
is to the right of it in merged columns B-E. The
circles are in column B, the corresponding letters
are in column C, and the answers are in column D.
There is a blank row before each question.

I need for the correct answer in each set to change
from test to test (obviously), so that's why I'd like
some sort of random generator. Can anyone help?

Thanks,

Debbie


1. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

2. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

3. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.

....

120. This will be a question...This row is merged from B-E.
O A. This will be a possible answer.
O B. This will be a possible answer.
O C. This will be a possible answer.
O D. This will be a possible answer.
O E. This will be a possible answer.
 
Hi Debbie

Try this routine. Edit to mirror your setup.

Sub Questionaire()
'Leo Heuser, 8 Aug. 2003
Dim BlockOffset As Long
Dim Counter As Long
Dim NumberOfAnswers As Long
Dim NumberOfQuestions As Long
Dim QuestionRange As Range

Set QuestionRange = Worksheets("Sheet3").Range("A2:E7") ' First question
NumberOfQuestions = 120
NumberOfAnswers = 5
BlockOffset = NumberOfAnswers + 2

QuestionRange.Columns(2).EntireColumn. _
Interior.ColorIndex = xlColorIndexNone

For Counter = 1 To NumberOfQuestions
QuestionRange.Cells((Int(Rnd * NumberOfAnswers) + 1) + 1, 2). _
Interior.ColorIndex = 3
Set QuestionRange = QuestionRange.Offset(BlockOffset, 0)
Next Counter

End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
I prefer to use worksheet functions as much as possible,
and if impossible then only use VB. Thus, the following
solution is proposed using worksheet functions.

First, let us get the coordinates straight. Since you are
using columns B through E for your data, I am using column
F for the solution.

Your first question starts in row 1; thus question number
is in cell A1; and the actual question is in range B1
through E1.

For every question, you have 5 possible answers (always 5).

If the above is correct and you are okay with it, then the
following solution will work.

In cell F1 key in the formula =RANDBETWEEN (1, 5).
Alternately the formula =INT(1+5*RAND()) will also work
quite fine.

The set of proposed answers for question 1 is proposed in
rows 2 through 6. Thus in cells F2 through F6 key in the
following formula:

=IF(MOD(ROW(),5)+1=F1,"Y"," ")

ATTENTION: In all these 5 cells the reference should be to
cell F1. Thus, if you are doing copy-paste, excel will
change F1 to F2, F3, F4, ... respectively. You will have
to ensure that in all the 5 cells the formula refers to
F1. ALSO - DO NOT USE ABSOLUTE REFERENCING BECAUSE THEN
THE FOLLOWING WILL NOT WORK.

Now, copy cells F1 through F6. Go to the cell in F column
where the 2nd question starts. Paste the copied cells.

Repeat the copy-paste for all your questions.

Now, for each question, in column F you will have a
randomly generated "Y" for one and only one answer. Of
course, since you are using random functions, the values
will change dynamically every time you change a cell.
Thus, if you want to make them static; I suggest that you
copy the entire F column and then do PASTE SPECIAL VALUES.

To automatically highlight the desired answer in red; use
conditional formatting.

Hope this meets your needs.


-----Original Message-----
Hi All,

I'm using XL97 on Win98, and I'm in the process of
creating a test blank. (See example below.)
What I'd like is to be able to run some code to
randomly select one circle in each set of answers
and color that circle red, so that I know to place
the correct answer next to the letter that corresponds
with the red circle.

There will be 120 questions. I gave names to a few of
the ranges, such as Q1Area, Q2Area, Q3Area, and then
I tried using Rand, but I have had no luck. I really
do not want to have to name 120 ranges, but I will,
if necessary.

The question number is in column A, and the question
is to the right of it in merged columns B-E. The
circles are in column B, the corresponding letters
are in column C, and the answers are in column D.
There is a blank row before each question.

I need for the correct answer in each set to change
from test to test (obviously), so that's why I'd like
some sort of random generator. Can anyone help?

Thanks,

Debbie


1. This will be a question...This row is merged from B-E.
Click to expand...
O A. This will be a possible answer.
Click to expand...
O B. This will be a possible answer.
Click to expand...
O C. This will be a possible answer.
Click to expand...
O D. This will be a possible answer.
Click to expand...
O E. This will be a possible answer.
Click to expand...
2. This will be a question...This row is merged from B-E.
Click to expand...
O A. This will be a possible answer.
Click to expand...
O B. This will be a possible answer.
Click to expand...
O C. This will be a possible answer.
Click to expand...
O D. This will be a possible answer.
Click to expand...
O E. This will be a possible answer.
Click to expand...
3. This will be a question...This row is merged from B-E.
Click to expand...
O A. This will be a possible answer.
Click to expand...
O B. This will be a possible answer.
Click to expand...
O C. This will be a possible answer.
Click to expand...
O D. This will be a possible answer.
Click to expand...
O E. This will be a possible answer.
Click to expand...
....

120. This will be a question...This row is merged from B- E.
O A. This will be a possible answer.
Click to expand...
O B. This will be a possible answer.
Click to expand...
O C. This will be a possible answer.
Click to expand...
 
Hi Leo & Akshay,

Thanks so much for your solutions. Both work perfectly,
exactly as I wanted. On this particular workbook, I
am going to go with the macro, since the user wants to
be able to just press a button. I have no doubt that I
will have future need for the worksheet function, also.

Again, thank you both.

Debbie

~~~~~~~~~~~~~~
 
Back
Top