| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for selecting random lines
Sorry to be such a nuisance but I am new at Excel macros and have been put
into a situation where I have to get some stuff up and running in a short time. I was conscripted into a volunteer position and could sure use some guidance. I was given a file with a total of 635 lines of questions and a macro (below) that is supposed to select 100 questions and answers (seperate documents) Sub RunRandom() Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub My understanding is that the questions are supposed to be printed on one document and the answers on another. I intend to buy a book so I don't have to rely on others but could really use some help until I get a bit of an understanding. Thanks sincerely Bob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for selecting random lines
I have been "volunteered" take over from a fellow (now unavailable to me) in
the preparation of an exam for voluntary certification. The contents are as follows. a-Original question number b- question c-choice of answer d-choice of answer e-choice of answer f-choice of answer g-choice of answer h-answer The exam is set up to be multiple choice. The person taking the exam gets the questions and the person marking gets the answer paper. Hope this gives a bit more information. Thanks Bob "Tushar Mehta" wrote in message news:MPG.1b1f1523162334e8989801@news-server... Not that answering these questions guarantees a satisfactory resolution to your problem, but... What are the contents of A:E before you run the macro? What is the purpose of the macro? What is the intent behind each of the major operations (AdvancedFilter, Sort, AutoFill) in the macro? What do you need help with? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry to be such a nuisance but I am new at Excel macros and have been put into a situation where I have to get some stuff up and running in a short time. I was conscripted into a volunteer position and could sure use some guidance. I was given a file with a total of 635 lines of questions and a macro (below) that is supposed to select 100 questions and answers (seperate documents) Sub RunRandom() Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub My understanding is that the questions are supposed to be printed on one document and the answers on another. I intend to buy a book so I don't have to rely on others but could really use some help until I get a bit of an understanding. Thanks sincerely Bob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for selecting random lines
I put comments in the code to suggest what the code would do.
Sub RunRandom() 'Select A2:A635 Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste ' Now B2:B635 should have the same information as column A ' next line turns off the marquee Application.CutCopyMode = False ' Filter column C1:C638 so only unique entries are displayed Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select ' sort all data contiguous to E2 based on values in Column B Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom ' Number column D sequentially starting in D2 Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub It doesn't appear to do what you describe and would overwrite the data in columns B and D for sure. They told you this was working? Notice that there is no code in it to ever address any sheet but the activesheet. It might be possible that the code assumes it is working on a sheet where the data has been copied to column E (and to the right) and additional formulas added in columns A:D (such as code to generate random numbers in column B). -- Regards, Tom Ogilvy "Bob" wrote in message . .. I have been "volunteered" take over from a fellow (now unavailable to me) in the preparation of an exam for voluntary certification. The contents are as follows. a-Original question number b- question c-choice of answer d-choice of answer e-choice of answer f-choice of answer g-choice of answer h-answer The exam is set up to be multiple choice. The person taking the exam gets the questions and the person marking gets the answer paper. Hope this gives a bit more information. Thanks Bob "Tushar Mehta" wrote in message news:MPG.1b1f1523162334e8989801@news-server... Not that answering these questions guarantees a satisfactory resolution to your problem, but... What are the contents of A:E before you run the macro? What is the purpose of the macro? What is the intent behind each of the major operations (AdvancedFilter, Sort, AutoFill) in the macro? What do you need help with? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry to be such a nuisance but I am new at Excel macros and have been put into a situation where I have to get some stuff up and running in a short time. I was conscripted into a volunteer position and could sure use some guidance. I was given a file with a total of 635 lines of questions and a macro (below) that is supposed to select 100 questions and answers (seperate documents) Sub RunRandom() Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub My understanding is that the questions are supposed to be printed on one document and the answers on another. I intend to buy a book so I don't have to rely on others but could really use some help until I get a bit of an understanding. Thanks sincerely Bob |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for selecting random lines
Tom
You described what is happening to a T I am thinking that there is quite a bit of missing code. The individual that was the keeper of this spreadsheet is not available to speak to (I think he departed from the voluntary position with bad feelings) It looks like I am going to have to buy a book and try to get things working. Appreciate your reply. Best Rgds Bob "Tom Ogilvy" wrote in message ... I put comments in the code to suggest what the code would do. Sub RunRandom() 'Select A2:A635 Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste ' Now B2:B635 should have the same information as column A ' next line turns off the marquee Application.CutCopyMode = False ' Filter column C1:C638 so only unique entries are displayed Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select ' sort all data contiguous to E2 based on values in Column B Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom ' Number column D sequentially starting in D2 Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub It doesn't appear to do what you describe and would overwrite the data in columns B and D for sure. They told you this was working? Notice that there is no code in it to ever address any sheet but the activesheet. It might be possible that the code assumes it is working on a sheet where the data has been copied to column E (and to the right) and additional formulas added in columns A:D (such as code to generate random numbers in column B). -- Regards, Tom Ogilvy "Bob" wrote in message . .. I have been "volunteered" take over from a fellow (now unavailable to me) in the preparation of an exam for voluntary certification. The contents are as follows. a-Original question number b- question c-choice of answer d-choice of answer e-choice of answer f-choice of answer g-choice of answer h-answer The exam is set up to be multiple choice. The person taking the exam gets the questions and the person marking gets the answer paper. Hope this gives a bit more information. Thanks Bob "Tushar Mehta" wrote in message news:MPG.1b1f1523162334e8989801@news-server... Not that answering these questions guarantees a satisfactory resolution to your problem, but... What are the contents of A:E before you run the macro? What is the purpose of the macro? What is the intent behind each of the major operations (AdvancedFilter, Sort, AutoFill) in the macro? What do you need help with? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry to be such a nuisance but I am new at Excel macros and have been put into a situation where I have to get some stuff up and running in a short time. I was conscripted into a volunteer position and could sure use some guidance. I was given a file with a total of 635 lines of questions and a macro (below) that is supposed to select 100 questions and answers (seperate documents) Sub RunRandom() Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub My understanding is that the questions are supposed to be printed on one document and the answers on another. I intend to buy a book so I don't have to rely on others but could really use some help until I get a bit of an understanding. Thanks sincerely Bob |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for selecting random lines
You do realize that if the person who takes the test answers the
questions from within XL itself, then there is no need for a grader! You could have XL do the grading itself. Take it a step further. The test could be made totally interactive. Have the person sit at the computer, and answer 100 questions that are drawn at random as the test is being taken! Anyway, back to your printed question scenario. Is there a specific requirement of how the documents are to be printed? Here's how you can do it *without* any programming! Suppose the database is Sheet1. And, as you indicated, the columns A through H are Q#, Q description, A1, A2, A3, A4, and correct answer (as a number 1 through 4. This information is assumed to be in rows 1:635, though in retrospect it might have been nice to create a header <g Anyway, on sheet 2, in A1:A635 enter =RAND(). In B1 enter =RANK(A1,$A $1:$A$635) Copy B1 to B2:B100 In C1 enter = IF(MOD(ROW()-1,7)=6,"",INT((ROW()-1)/7)+1 &IF(MOD(ROW()- 1,7)=0,"","-"&(MOD(ROW()-1,7)))) In D1 enter =IF(MOD(ROW(),7)= 0,"",INDEX(Sheet1!$B$1:$G$635,INDEX(Sheet2!$B$1:$B $100,INT((ROW()-1)/7) +1),MOD(ROW(),7))) Copy C1:D1 to rows 2:700. Extend column D so that it occupies an aesthetically pleasing amount of space when printed. Then, select D and select Format | Cells... | Alignment | and select the 'Wrap text' option Print columns C and D. The result will look like: 1 Question 186 1-1 Q 186 A 1 1-2 Q 186 A 2 1-3 Q 186 A 3 1-4 Q 186 A 4 1-5 Q 186 A 5 2 Question 525 2-1 Q 525 A 1 2-2 Q 525 A 2 2-3 Q 525 A 3 2-4 Q 525 A 4 2-5 Q 525 A 5 3 Question 370 3-1 Q 370 A 1 3-2 Q 370 A 2 3-3 Q 370 A 3 3-4 Q 370 A 4 3-5 Q 370 A 5 And, the person taking the test can circle whichever answer s/he deems correct. For the grading key, activate Sheet3. In A1enter =ROW() In B1 enter =Sheet2!B1 In C1 enter =INDEX(Sheet1!$H $1:$H$635,Sheet3!B1) Copy A1:C1 to rows 2:100. Print the sheet for the answer key. Press F9 to generate a completely different questionnaire. Do remember one *very, very* important point. You must print both the question sheet and the answer sheet *without* an intervening recalculation. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have been "volunteered" take over from a fellow (now unavailable to me) in the preparation of an exam for voluntary certification. The contents are as follows. a-Original question number b- question c-choice of answer d-choice of answer e-choice of answer f-choice of answer g-choice of answer h-answer The exam is set up to be multiple choice. The person taking the exam gets the questions and the person marking gets the answer paper. Hope this gives a bit more information. Thanks Bob "Tushar Mehta" wrote in message news:MPG.1b1f1523162334e8989801@news-server... Not that answering these questions guarantees a satisfactory resolution to your problem, but... What are the contents of A:E before you run the macro? What is the purpose of the macro? What is the intent behind each of the major operations (AdvancedFilter, Sort, AutoFill) in the macro? What do you need help with? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Sorry to be such a nuisance but I am new at Excel macros and have been put into a situation where I have to get some stuff up and running in a short time. I was conscripted into a volunteer position and could sure use some guidance. I was given a file with a total of 635 lines of questions and a macro (below) that is supposed to select 100 questions and answers (seperate documents) Sub RunRandom() Range("A2:A635").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("C1:C638").AdvancedFilter Action:=xlFilterInPlace, Unique:= _ True Range("E2").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Range("D2").Select ActiveCell.FormulaR1C1 = "1" Range("D3").Select ActiveCell.FormulaR1C1 = "2" Range("D2:D3").Select Selection.AutoFill Destination:=Range("D2:D638"), Type:= _ xlFillDefault Range("D2:D151").Select Range("A1").Select End Sub My understanding is that the questions are supposed to be printed on one document and the answers on another. I intend to buy a book so I don't have to rely on others but could really use some help until I get a bit of an understanding. Thanks sincerely Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| the formula for random selecting | Excel Discussion (Misc queries) | |||
| Need macro for copying random lines of data | Excel Discussion (Misc queries) | |||
| Selecting Random Data | Excel Worksheet Functions | |||
| Selecting Random Data | Excel Worksheet Functions | |||
| selecting random records | Excel Programming | |||
![Bob[_57_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
