Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomly Select Cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomly Select Cells
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. "Debbie" skrev i en meddelelse m... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomly Select Cells
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. 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lottery function - how to randomly select cells | Excel Worksheet Functions | |||
How do I randomly select cells regardless of cell content? | Excel Worksheet Functions | |||
what can I do to randomly select cells that contain text? | Excel Worksheet Functions | |||
does ctrl f select cells randomly? | Excel Discussion (Misc queries) | |||
how to randomly select a name in a list and then the select the ne | Excel Worksheet Functions |