CODEWITHSUNDEEP
vbaexcelvlookup
Kes Perron
Kes Perron

Reputation: 477

Using VBA to compare 2 lists

Every month, I export 2 lists from an SAP database that get saved as seperate Excel files. List A contains all defects found in the last month. List B contains all of the defects caused by an outside vendor. I do a lot of formatting to ListA before it is a complete report, and I have a macro that does most of it perfectly. I just can't seem to get the list comparison piece working.

My manual process is to insert a new column D in List A, type =VLOOKUP(A2,ListB!$A$2:$N$946,1,FALSE) into cell D2, and copy it down to the end (the range of the look-up table varies depending on the size of ListB). I would then filter the list to exclude "#N/A"s and change the value of the "Cause" column (now column E) to "Vendor" for the remaining rows. FInally, I delete the VLOOKUP column.

I thought automating this process would be easy, but it has given me several migranes. I could get the job done with nested For Loops like this, but I feel that there has to be a more efficient way.

First, I tried the Vlookup Aplication Function within a For Loop:

'ListA and ListB declared as Worksheets and set
Dim aRange as Range, bRange as Range
Dim last row As Integer
Set aRange = ListA.Range("A1")
Set aRange = Range(aRange, aRange.End(xlToRight))
Set aRange = Range(aRange, aRange.End(xlDown))
Set bRange = ListB.Range("A1")
Set bRange = Range(bRange, bRange.End(xlToRight))
Set bRange = Range(bRange, bRange.End(xlDown))

'I'm skipping over the stuff that would be here but don't need help with

lastrow = ListA.Range("A1").End(xlDown).Row
For i = 2 To lastrow
    ListA.Cells(i, 4).Value = Application.WorksheetFunction.VLookup(ListA.Cells(i, 1), bRange, 3, False)
    If ListA.Cells(i, 4).Value <> "#N/A" Then ListA.Cells(i, 5).Value = "Vendor"
Next i

Unfortunately, this method encountered a Runtime error at the first cell that would have returned a #N/A error.

For my second attempt, I recorded a macro while going through my manual process and then tried changing the returned code from this:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet1!R2C1:R946C14,1,FALSE)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D1033"), Type:=xlFillDefault
Range("D2:D1033").Select

to this:

Set aRange = ListA.Range("D2")
Set aRange = Range(aRange, aRange.End(xlDown))
Set bRange = ListB.Range("A1")
Set bRange = Range(bRange, bRange.End(xlToRight))
Set bRange = Range(bRange, bRange.End(xlDown))
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-3]," & bRange & ",1,FALSE)"
Range("D2").AutoFill Destination:=aRange, Type:=xlFillDefault

This method isn't working, according to the Runtime Error, due to a type missmatch. I can only assume that it doesn't like me sticking a range varaible in the middle of the formula, but I don't know of another way of making sure that the look-up table is the right size.

Any help (which approach is more efficient, how to get one of these working, or even a different approach I hadn't thought of) is greatly appreciated.

Upvotes: 0

Views: 1548

Answers (1)

SeanC
SeanC

Reputation: 15923

the runtime error is actually correct from the WorksheetFunction.VLookup function - that's the equivalent of #N/A! that the excel formula would return. By checking for the error, we can find the N/A values (or, as we do in this case, by checking for success, we can write out the Vendor string)

On Error Resume Next 'don't error out on failure to find

For i = 2 To lastrow
   Err.Clear 
   'clear out errors, as we don't want to see whatever the previous error was
   ListA.Cells(i, 4).Value = _
       Application.WorksheetFunction.VLookup(ListA.Cells(i, 1), bRange, 3, False)
   ' if no error, then we have a vendor
   If Err.Number=0 Then ListA.Cells(i, 5).Value = "Vendor"
Next i

'reset error trapping back to default 
'(or to whatever your normal error handling is
On Error Goto 0

Upvotes: 1

Related Questions