Hello,
I have a problem with some find, copy and paste coding. On a userform, there is a listbox for the academic year (acad_year) and a textbox for the unique record id (id) which supplies the two variables for this part of the coding. Both of these are working correctly and storing the correct values. The coding looks for these two values in a list in sheet ws, then finds the corresponding id and acad_year in the list in sheet ws2. It then copies and pastes ranges from the relevant row (row i) from ws2 into ws (row i).
The issue is that the coding is not finding the correct acad_year. Where an id has two records in ws2, a 17/18 record and a 18/19 record, it is not finding, copying and pasting the 18/19 record. It is pasting the first record it finds for id in ws2 which is the 17/18 one. (I checked this by switching the records round.)
I think the error may be somewhere on this line, but I don't know how to rectify it. (Range E is the column containing the id in ws2.)
I would be very grateful for any help on this. The relevant parts of my code are below:
I have a problem with some find, copy and paste coding. On a userform, there is a listbox for the academic year (acad_year) and a textbox for the unique record id (id) which supplies the two variables for this part of the coding. Both of these are working correctly and storing the correct values. The coding looks for these two values in a list in sheet ws, then finds the corresponding id and acad_year in the list in sheet ws2. It then copies and pastes ranges from the relevant row (row i) from ws2 into ws (row i).
The issue is that the coding is not finding the correct acad_year. Where an id has two records in ws2, a 17/18 record and a 18/19 record, it is not finding, copying and pasting the 18/19 record. It is pasting the first record it finds for id in ws2 which is the 17/18 one. (I checked this by switching the records round.)
I think the error may be somewhere on this line, but I don't know how to rectify it. (Range E is the column containing the id in ws2.)
Code:
Set pop_fin = ws2.Range("E7:E1000").Find(ws.Range("F" & i).Value, , , xlWhole, , , False, , False)
I would be very grateful for any help on this. The relevant parts of my code are below:
Code:
Public Sub Pop_Stu_list()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim pop_fin As Range
Dim lItem As Long
Dim acad_year As String
Dim lItem2 As Long
Dim faculty As String
Dim id As String
Set ws = ThisWorkbook.Sheets("student list")
Set ws2 = ThisWorkbook.Sheets("QLS Download")
For lItem = 0 To UserForm3.ListBox1.ListCount - 1
If UserForm3.ListBox1.Selected(lItem) Then
acad_year = UserForm3.ListBox1.List(lItem)
End If
Next lItem
For lItem2 = 0 To UserForm3.ListBox2.ListCount - 1
If UserForm3.ListBox2.Selected(lItem2) Then
faculty = UserForm3.ListBox2.List(lItem2)
End If
Next lItem2
id = UserForm3.TextBox1.Value
Finalrow = Sheets("student list").Range("A902").End(xlUp).Row
Finalrow2 = Sheets("QLS Download").Range("E902").End(xlUp).Row
Application.ScreenUpdating = False
If id <> "" Then
With ws
For i = 7 To Finalrow
If .Cells(i, 9).Value = acad_year And _
.Cells(i, 6).Value = id Then
Set pop_fin = ws2.Range("E7:E1000").Find(ws.Range("F" & i).Value, , , xlWhole, , , False, , False)
pop_fin.Offset(, -4).Resize(, 4).Copy
ws.Range("B" & i).PasteSpecial xlPasteValues
pop_fin.Offset(, 5).Resize(, 1).Copy
ws.Range("H" & i).PasteSpecial xlPasteValues
pop_fin.Offset(, 7).Resize(, 3).Copy
ws.Range("J" & i).PasteSpecial xlPasteValues
End If
Next i
End With
'further coding not included ...