lookup with VBA

[Owen98]

Hi,
I'm hoping someone could help with this coursework I have and am really perplexed with.
I've managed all other aspects but this last part.
Basically, in one worksheet I have a list of student ID's in the y-axis and there chosen module codes in the x-axis.
In a second sheet I have student ID's again in the y-axis, and chosen modules in the x-axis

1st photo = rq
2nd photo = PartC

Basically, using the second example, using the ID (B712547) I need to find the same ID in the second worksheet, and put an "x" in the resulting cell.
i.e B712547 has chosen module SBC110, and so an "x" needs to be placed in the cell K15.
This part is easy, however, it needs to loop through all the IDs. Additionally, the data in rq, can change, and so the code needs to be flexible to change with the different data.

Regards, Owen.

 

[Owen98]

(this is the code ive so far written. Basically, it reads each student ID and their requests. And then for any student who has made a request, I've cleared the "x's" in part C K:Y as these are the optional modules. B:J are compulsory modules and so cannot be edited.)
additionally, I cannot edit the excel worksheet

Sub Coursework()


Dim rq As Worksheet, PartC As Worksheet, accepted As Worksheet
Dim Option1 As Variant, Option2 As Variant, Option3 As Variant, Option4 As Variant, Option5 As Variant, Option6 As Variant, Option7 As Variant, Option8 As Variant, Option9 As Variant, Option10 As Variant
Dim IDRow As Variant, ID As Variant, PID As Variant, SIDRow As Variant, SID As Variant, vlookupresult As String
Dim i As Integer, k As Integer, j As Integer
Dim finalrow As Integer
Dim SBC100 As Variant, SBC110 As Variant, SBC120 As Variant, SBC130 As Variant, SBC140 As Variant, SBC150 As Variant, SBC160 As Variant, SBC170 As Variant, SBC180 As Variant, SBC190 As Variant, SBC200 As Variant, SBC210 As Variant, SBC220 As Variant, SBC230 As Variant, SBC240 As Variant

'Foundation work - items which remain constant
'Need to change 'ThisWorkBook' to be anything labelled Updats.xlsx
'Set Requests tab to 'rq'
Set rq = ThisWorkbook.Worksheets("Requests")
Set PartC = ThisWorkbook.Worksheets("PartC")
finalrow = PartC.Range("A1").End(xlDown).Row



'Part 1 - Read the requestsand store each ID and option choice, then perform a vlookup to match the student making requsts to that of students in partC
'Establish the variables as options
IDRow = 1
Do Until rq.Range("A2").Cells(IDRow, 1) = ""
    DoEvents
        'Find the data based on ID
        ID = rq.Range("A2").Cells(IDRow, 1)
            If (Len(ID) = 7) And (ID Like "B######") Then              'if right length and order e.g. B721542
              Option1 = rq.Range("A2").Cells(IDRow, 2)
                Option2 = rq.Range("A2").Cells(IDRow, 3)
                  Option3 = rq.Range("A2").Cells(IDRow, 4)
                    Option4 = rq.Range("A2").Cells(IDRow, 5)
                      Option5 = rq.Range("A2").Cells(IDRow, 6)
                        Option6 = rq.Range("A2").Cells(IDRow, 7)
                          Option7 = rq.Range("A2").Cells(IDRow, 8)
                            Option8 = rq.Range("A2").Cells(IDRow, 9)
                              Option9 = rq.Range("A2").Cells(IDRow, 10)
                                Option10 = rq.Range("A2").Cells(IDRow, 11)
                               
                                       
                                        'If function stating if partc."z2" (Like "B######) then replace the module choices
                                        For i = 2 To finalrow
                                        'Vlookup - match ID in requests to ID in part C and using IF function, if vlookup is true,then transpose option choices from rq to part C.
                                        On Error Resume Next
                                        PartC.Cells(i, 26) = Application.WorksheetFunction.VLookup(PartC.Cells(i, 1), rq.Range("A1:K500"), 1, False)
                                        On Error GoTo 0
                                        Next i
                                                                                                                                                            
            Else
            Option1 = ""
              Option2 = ""
               Option3 = ""
                 Option4 = ""
                   Option5 = ""
                     Option6 = ""
                       Option7 = ""
                         Option8 = ""
                           Option9 = ""
                             Option10 = ""
            End If
           
        IDRow = IDRow + 1
       
Loop
'End of part 1 - Complete - Remeber additional error checking, and to edit so rq can be found in a seperate workbook and worksheet labelled "Requests"

'Start of part 2 - Clear contents of optional modules for all part C students who have made a second request, using vlookup (remove vlookup as well)
Application.ScreenUpdating = False
For k = 2 To finalrow
If Cells(k, 26) <> "" Then
PartC.Range("K" & k & ":Y" & k).ClearContents
End If
Next k
Application.ScreenUpdating = True
'End of part 2

'Part 3 - using the variables created in part 1, and the vlookup, add the option chices from requests to part c (vlookup may be still needed, so perhapshide the cell instead of deleting (PartC.Column("Z").hidden)





MsgBox "error checking"
Set rq = Nothing
Set PartC = Nothing
End Sub