I have some code that vlookups between two different workbooks. It works perfectly when there is no error handling (skips when finds an error).
However, now I need to return a value from another column (Column A) when the error occurs. The code I wrote will get to the first error. Return the value I want, but then the values afterwards are wrong (ie not vlookuping correctly).
My code is as follows:
'First VLOOKUP ' Set the table values Set Table1 = sh.Range("T2:T2", sh.Range("T2:T2").End(xlDown)) ' what we are searching for to last row Set Table2 = wb2.Sheets("Franchisee to BAM Mapping").Columns("A:L") 'the data we want to retrieve ' Set our row and Column ' this is where we want to populate the data row = sh.Range("U2").row column = sh.Range("U2").column ' Start vlooking up On Error Resume Next ' so that we don't stop when nothing is found. ctr = 0 For Each cl In Table1 sh.Cells(row, column) = Application.WorksheetFunction.VLookup(cl, Table2, 12, False) If Err.Number <> 0 Then sh.Range("U" & row).Value = sh.Range("A" & row).Value End If row = row + 1 ctr = ctr + 1 Next cl