Hello all,
I am currently trying to write a macro that will copy any data entries missing from 'Sheet1' in my excel workbook that are in 'Sheet2'. The missing entries are to be copied into a brand new sheet 'Sheet3'. As per my code below, the logic I used was to do a VLookup searching for the serial number in column D of Sheet 2, and compare them to those in Sheet 1. If the result of the VLookup was #N/A, then I would like the entire row of data associated with the serial number in Sheet 2 to be copied to Sheet 3.
However, it seems that all of the VLookup results are giving an error, and the macro is copying all of the entries from Sheet 2 to Sheet 3. I have tried using the MsgBox function directily after the VLookup to show me the result, but the I get a Type Mismatch error (I am assuming this is because the VLookup result is a Variant). So then I tried the function MsgBox CStr(Missing), as can be seen below in the code, and this brings up the MsgBox but all it contains is 'Error 2015'.
I hope I have been clear with my explanation and I appreciate any help!
Below is my code:
Sub FindMissing()
Dim LastRow As Integer
Dim Missing As Variant
Dim RFAnum As Variant
Worksheets("Sheet2").Select
Range("D3").Select
Do While IsEmpty(Selection.Value) = False
Selection.Offset(1, 0).Select
Loop
LastRow = Selection.Row - 1
For i = 4 To LastRow
Worksheets("Sheet2").Select
Range("D" & i).Select
RFAnum = Selection
ActiveCell.EntireRow.Copy
Missing = Application.VLookup("RFAnum", "Sheet1!A3:AC250", 4, False)
MsgBox CStr(Missing)
If IsError(Missing) Then
Sheets("Sheet3").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial
End If
Next i
End Sub
Bookmarks