Hi,
I need to create a nested loop involving a find, copy and paste but don't know how to. There are two sheets, ws and ws2. In ws, where acad_year is a certain value, the first loop goes through the list looking for pop_fin (the unique id of the record) and matching it up in ws2. However, there can be more than one pop_fin record in ws2 based on year. I therefore need it to find the correct year in ws2 which matches acad_year in ws. So, I then need to do a nested loop, going through the pop_fin records found within each i, and iterating on year until year = acad_year. Year is found within column K in ws2.
I need to replace the following line with the nested loop as at the moment it will only consider the first pop_fin record it comes across. I don't know how to code this:
Any help to get this working would be much appreciated. My code so far is below:
I need to create a nested loop involving a find, copy and paste but don't know how to. There are two sheets, ws and ws2. In ws, where acad_year is a certain value, the first loop goes through the list looking for pop_fin (the unique id of the record) and matching it up in ws2. However, there can be more than one pop_fin record in ws2 based on year. I therefore need it to find the correct year in ws2 which matches acad_year in ws. So, I then need to do a nested loop, going through the pop_fin records found within each i, and iterating on year until year = acad_year. Year is found within column K in ws2.
I need to replace the following line with the nested loop as at the moment it will only consider the first pop_fin record it comes across. I don't know how to code this:
Code:
If pop_fin.Offset(, 6).Value = acad_year Then
Code:
Public Sub Overwrite_Stu_data()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim pop_fin As Range
Dim acad_year As String
Dim year As String
Set ws = ThisWorkbook.Sheets("2017-18 student list")
Set ws2 = ThisWorkbook.Sheets("QLS 17-18 Download")
Finalrow = Sheets("2017-18 student list").Range("A902").End(xlUp).Row
Finalrow2 = Sheets("QLS 17-18 Download").Range("E902").End(xlUp).Row
Application.ScreenUpdating = False
'...
If faculty = "All faculties" Then
With ws
For i = 7 To Finalrow
If .Cells(i, 9).Value = acad_year Then
Set pop_fin = ws2.Range("E7:E1000").Find(ws.Range("F" & i).Value, , , xlWhole, , , False, , False)
If Not pop_fin Is Nothing Then
If pop_fin.Offset(, 6).Value = acad_year Then
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
End If
End If
Next i
End With
