Setting up a test for students to get the right answer

[RodneyW]

I have a sheet named DATA, I’ll use this as my source for information populating another sheet called CITY. From the DATA sheet, I’ll reference $D$4:$D$123 that contains a list of cities. Cells $E$4:$E$123 have a list of corresponding airport codes for each city listed in column D. I’m testing to see if students can enter the airport code associated with each city.

• On the sheet named CITY, in cell C5, I’d like it to randomly select one item from DATA!$D$4:$D$123.
• Cell C4 would have a button with the text “NEXT” and when clicked, it would randomly select another item from DATA!$D$4:$D$123
• Cell D5 is where students can enter their answer.
• Cell E5 will be blank until the student enters their answer in D5. Once a student enters data into D5, E5 will display the corresponding airport code from DATA!$E$4:$E$123.

Thanks in advance.

 

[RodneyW]

I'm getting close...
Cell E5 solution is a simple VLOOKUP.
Cell C5 has this code: =INDEX(Data!$D3:$D122,RANDBETWEEN(1,COUNTA(Data!$D3:$D122))) This works BUT anytime I enter data into D5, C5 changes. This doesn't work because I need the process to test whether the value in D5 matches the value in E5. How can I make C5 stay the same until "NEXT" in C4 is clicked... I need codes for both C4 and C5.

Thanks

 

[Ezguy4u]

Rodney let's get the ball rolling. Now I did install a command button over C5 but it doesn't show up. If you have a problem with installing and linking a command button let us know. Now usually this generates more questions than answers but we have to start some place.

Option Explicit

Sub NextCity()
'  Note when you finally enter all the data you will have to update the D10 to what ever number in the last cell
Sheets("City").Range("C5").Formula = "=INDEX(Data!$D3:$D10,RANDBETWEEN(1,COUNTA(Data!$D3:$D10)))"
'Just in case somebody deletes the formula in E5, you can run this line again
'Sheets("City").Range("E5").Formula = "=IF(D5="""","""",INDEX(Data!D3:E10,MATCH(City!C5,Data!D3:D10,0),2))"
'the next 3 lines deletes the formula in cell C5
Sheets("City").Range("C5").Copy
Sheets("City").Range("C5").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("City").Range("D5") = ""
Range("D5").Select

End Sub

24-07-20 rev C.xlsm
	C	D	E
1
2
3
4
5	New York
City

Cell Formulas
Range	Formula
E5	E5	=IF(D5="","",INDEX(Data!D3:E10,MATCH(City!C5,Data!D3:D10,0),2))

24-07-20 rev C.xlsm
	D	E
3	Los Angeles	LAX
4	San Francisco	SFO
5	New York	JFK
6	Phoenix	PHX
7	Denver	DEN
8	Miami	MIA
9	Chicago	ORD
10	Boston	BOS
Data

 

[Peter_SSs]

A marginally different approach.
You can use a button at C4 if you want but I just typed the word "NEXT" into cell C4 and used the double-click event code below to make the relevant changes. To implement this ..

1. Enter the word NEXT into C4 of the 'City' sheet.
2. Right click the 'City' sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window & test by double-clicking C4 on the 'City' sheet.
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim n As Long
  
  Const DataAddress As String = "D4:E124" '<- Note this includes BOTH columns of data
  
  If Target.Address(0, 0) = "C4" Then
    Cancel = True
    Randomize
    n = 1 + Int(Rnd() * Sheets("Data").Range(DataAddress).Rows.Count)
    Range("C5:E5").Formula = Array("=INDEX(Data!" & DataAddress & "," & n & ",1)", _
                                    "", _
                                    "=IF(D5="""","""",INDEX(Data!" & DataAddress & "," & n & ",2))")
  End If
End Sub

 

[RodneyW]

Thank you all for your responses. I'm on vacation with my wife right now and I'll get back to trying your solutions in the next couple days.

thanks again

 

[Ezguy4u]

Peter, I really enjoy this sight as I keep learning new things. Looking at your program I can see how I can improve on my programing.

 

[RodneyW]

Peter and EZ, Thank you both for your help. I got it working using Peters suggestion, it's the first one I tried. I modified the cell references because I changed the structure of the data a little, but in any case it's working. The page referenced tests "Here's the city, tell me the airport code". I have another tab that tests "Here's the airport code, tell me the city". I've got another question regarding keeping score but that will come in another post. Thanks again to both of you.

=====

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim n As Long
 
  Const DataAddress As String = "D7:E128" '<- Note this includes BOTH columns of data
 
  If Target.Address(0, 0) = "H18" Then
    Cancel = True
    Randomize
    n = 1 + Int(Rnd() * Sheets("Data").Range(DataAddress).Rows.Count)
    Range("C18:E18").Formula = Array("=INDEX(Data!" & DataAddress & "," & n & ",1)", _
                                    "", _
                                    "=IF(D18="""","""",INDEX(Data!" & DataAddress & "," & n & ",2))")
  End If
End Sub

=====

 

[RodneyW]

Thanks again

 

[Peter_SSs]

Glad the suggestion helped.
BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[RodneyW]

Thank you