Flagging A Used Quiz Question In Excel

[cssriraman]

I am working on a quiz program in excel and want to be able to “flag” the questions that have been used. The main table has two columns at the beginning that give each line a random number and rank them. Then besides the question and possible answers, there is a column at the end of the table where I want the program to put a “X” when the question has been used.

The process takes ranking 1-10 (via formulas) to a smaller table which is then copied and “paste values” into what I call the “working” table from which the quiz actually gets the information.

What I want to do is once the data is in the “working” table is to have it lookup the matching data in the main table and when it finds it, to place that “X” in the last column.

I have had no problem with vlookup formulas either directly in a cell or using VBA code to put the formula in a cell and finding the data, but I am at a loss as to how to now tell it to go paste a “X” in that column in the main table based on that finding.

I am using Excel 2002 with SP3 on windows xp

Thanks for any help you can give.

[Webtest]

cssriraman ...

I don't have time to focus on exactly which page you are trying to write to, and you say you "have no trouble with the lookup forumlas", so here is some code that might help you when working with multiple sheets. I hope this helps ... fill in your own names where there are bold italics ...

Sub Macro1()
    Dim shtWork As Worksheet    'Handle for the "Work" sheet
    Dim shtMain As Worksheet    'Handle for the "Main" sheet
    Dim aRow As Long            'Row number
    Const colX As String = "C"  'This is the column where the "X" goes
    
    Set shtWork = Workbooks("WhichBook.xls").Worksheets("WorkSheetName")
    Set shtMain = Workbooks("WhichBook.xls").Worksheets("MainSheetName")
    
    aRow = 2 'Set the row based on your Lookup results
    shtMain.Cells(aRow, colX).Value = "X"
        ' ---  or  ---
    aRow = 3 'Set the row based on your Lookup results
    shtWork.Cells(aRow, colX).Value = "X"

    'Clean up objects at the end of your work
    set shtWork = Nothing
    set shtMain = Nothing
End Sub