VLookup for alphanumeric values resulting in error

[excelMTL]

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

[tigeravatar]

excelMTL,

Welcome to the forum! In the future, please wrap your code in code tags. See link in my sig for how.
As for your question, give this a try:

Please Login or Register  to view this content.

[excelMTL]

Thank you tigeravatar for the quick response, it worked like a charm! I shall be sure to wrap my code in code tags in the future!!

[tigeravatar]

You're very welcome