I've been struggling with this for sometime and can't find the answer.
I have a range of data in my Reference sheet, which holds specific information on certain members of a club, I then want to loop through each entry starting at A3 and compare it against the Data sheet which holds all information of all members. If the reference file entry is not found on the data sheet I want to miss that out but continue searching through the last of the reference file. So for example the reference file has A3:A20 at A10, for example, that person's name is not found in the datasheet I then want to continue searching through. Please see the code I have so far.
also... if there is a more elegant way of doing this then all ears!!
I have a range of data in my Reference sheet, which holds specific information on certain members of a club, I then want to loop through each entry starting at A3 and compare it against the Data sheet which holds all information of all members. If the reference file entry is not found on the data sheet I want to miss that out but continue searching through the last of the reference file. So for example the reference file has A3:A20 at A10, for example, that person's name is not found in the datasheet I then want to continue searching through. Please see the code I have so far.
also... if there is a more elegant way of doing this then all ears!!
VBA Code:
Sub myReportaa()
Dim dSheet As Worksheet
Dim rptSheet As Worksheet
Dim refSheet As Worksheet
Dim custname As Variant
Set dSheet = ThisWorkbook.Sheets("Data")
Set rptSheet = ThisWorkbook.Sheets("Report")
Set refSheet = ThisWorkbook.Sheets("Reference")
rptLR = rptSheet.Cells(Rows.Count, 1).End(xlUp).Row
rptSheet.Range("a2:g" & rptLR).ClearContents
lastrow = dSheet.Cells(Rows.Count, 1).End(xlUp).Row
y = 2 'starting row
x = 3
custname = refSheet.Cells(x, 1)
Do
rptSheet.Cells(y, 1) = dSheet.Cells(x, 1)
rptSheet.Cells(y, 2) = dSheet.Cells(x, 2)
rptSheet.Cells(y, 3) = dSheet.Cells(x, 4)
rptSheet.Cells(y, 4) = dSheet.Cells(x, 16)
rptSheet.Cells(y, 5) = dSheet.Cells(x, 18)
rptSheet.Cells(y, 6) = dSheet.Cells(x, 19)
rptSheet.Cells(y, 7) = dSheet.Cells(x, 20)
y = y + 1
x = x + 1
custname = refSheet.Cells(x, 1)
Loop While dSheet.Cells(x, 2) = custname
rptSheet.Range ("A1")
End Sub