Compare two ranges to find non match and exceptions using collections run-time error 1004

Question

I've been following the example from this solution Excel vba - Compare two ranges and find non matches and tried to combine it with this solution http://dailydoseofexcel.com/archives/2004/05/18/listing-unique-items-with-collections/

However, when I run the code included below, I get a run-time error '1004': Unable to get the Vlookup property of the WoorkSheetClass. How do I get past that error, so that I can remove the rows that does not exist in my cUniqueInput?

Sub RemoveYdelser()

'Get Range From Datark To get range of Ydelses Group
Set YdelsesStart = Worksheets("DATAARK").Range("O8")
Set YdelsesSlut = Worksheets("DATAARK").Range("P8")

Dim LastRow As Long
LastRow = Worksheets("INPUT_MASTERDATA").Range("L" & Rows.Count).End(xlUp).Row

'Variable to get ydelser from Input_masterdata
Dim cUniqueInput As Collection
Dim Rng As Range
Dim Cell As Range
Dim shInput As Worksheet
Dim vNum As Variant

Set shInput = Worksheets("INPUT_MASTERDATA")
Set Rng = shInput.Range("L2:L" & LastRow)
Set cUniqueInput = New Collection

'Get unique values from Input_masterdata, column L
On Error Resume Next
    For Each Cell In Rng.Cells
        cUniqueInput.Add Cell.Value, CStr(Cell.Value)
    Next Cell
On Error GoTo 0

'Add exceptions for master ydelser not listed in column L
cUniqueInput.Add "x", CStr("x")
cUniqueInput.Add "R63011209", CStr("R63011209")
cUniqueInput.Add "R63011206", CStr("R63011206")

'Variable to get ydelser from Summary
Dim cUniqueYdelser As Collection
Dim RngYdelser As Range
Dim CellYdelser As Range
Dim shOpsumering As Worksheet
Dim xNum As Variant

Set shOpsumering = Worksheets("Summary")
Set RngYdelser = shOpsumering.Range("C" & YdelsesStart, "C" & YdelsesSlut)
Set cUniqueYdelser = New Collection

'Get unique values from Summary, column C
On Error Resume Next
    For Each CellYdelser In RngYdelser.Cells
        cUniqueYdelser.Add CellYdelser.Value, CStr(CellYdelser.Value)
    Next CellYdelser
On Error GoTo 0

'Does the values from cUniqueInput exist in cUniqueYdelser
For y = 1 To cUniqueYdelser.Count
    'If the value of a row in cUniqueYdelser does not exist in cUniqueInput, then remove entire row
    'This is where I get the runtime error
    If Application.WorksheetFunction.VLookup(cUniqueYdelser(y), cUniqueInput, 1, False) = "#N/A" Then 
        With Worksheets("OPSUMMERINGSARK").Cells
            .AutoFilter Field:=3, Criteria1:=cUniqueYdelser(y)
            .Range("C" & YdelsesStart, "C" & YdelsesSlut).EntireRow.Delete
        End With
    End If
Next y

barryleajo · Accepted Answer · 2014-11-05 09:48:19Z

0

Try changing:

If Application.WorksheetFunction.VLookup(cUniqueYdelser(y), cUniqueInput, 1, False) = "#N/A" Then

To

With Application
    If .ISNA(.VLookup(cUniqueYdelser(y), cUniqueInput, 1, False) Then
      ...whatever
End with

Untested in your context of the Collection

answered Nov 5, 2014 at 9:48

barryleajo

1,9622 gold badges13 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Philip Over a year ago

I get a Run-time error 1004: Application-defined or object-defined error. Is the problem that Im trying to Vlookup on two collections?

barryleajo Over a year ago

Ah, likely so. VLOOKUP expects a Range as its second argument. A couple of options might be to write your collection to a temporary Range and continue with VLOOKUP, or simply loop through the cUniqueInput collection to find (or not) cUniqueYdelser(y)

Ron Rosenfeld · Accepted Answer · 2014-11-05 13:41:02Z

0

To determine if an item exists in a collection, try to retrieve the item by Key, and see if there is an error (#9 - subscript out of range).

You could also set a reference to Microsoft Scripting Runtime and use a Dictionary instead of a collection. You can then use the Exists method to see if a key exists.

answered Nov 5, 2014 at 13:41

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

Philip · Accepted Answer · 2014-11-05 14:53:59Z

I found the solution. The problem was that I was trying to use Vlookup on a collection. Instead I looped through the collections like this

'Does the values from cUniqueInput exist in cUniqueYdelser
For Each ydelse In cUniqueYdelser
    Dim itemFoundYdelse As Boolean
    itemFoundYdelse = False

    For Each inputYdelse In cUniqueInput
        If ydelse = inputYdelse Then
            itemFoundYdelse = True
        Else
        'Item not found
        End If
    Next inputYdelse

    If itemFoundYdelse = False Then
            With Worksheets("Summary").Cells
                .AutoFilter Field:=3, Criteria1:=ydelse
                .Range("C" & YdelsesStart, "C" & YdelsesSlut).EntireRow.Delete
            End With
    End If
Next ydelse

Collectives™ on Stack Overflow

Compare two ranges to find non match and exceptions using collections run-time error 1004

3 Answers 3

2 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Linked

Related