CODEWITHSUNDEEP
excelvba
aMadNoob
aMadNoob

Reputation: 25

How to fix: compare two sheets & output matches code?

I have a current code that is supposed to compare tables in sheet1 to sheet2 and output the matches in another sheet(sheet3). When trying to run the code, I am getting "Type mismatch" error" and I'm not sure what is wrong nor how to fix it...

Picture of VBA code and the second table/sheet: enter image description here

Sub CompareSolve()
Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
    For i = 2 To UBound(ar, 1)
       .Item(ar(i, 1)) = Empty
    Next

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
    If .exists(ar(i, 1)) Then
       n = n + 1
            For j = 1 To UBound(ar, 2)
                   ar(n, j) = ar(i, j)
           Next j
   End If
Next i
End With
Sheet3.Cells(10, 8).Resize(n, UBound(ar, 2)).Value = ar
End Sub

These two tables are in the same position on the sheets for these 2 sheets

Upvotes: 0

Views: 62

Answers (1)

aMadNoob
aMadNoob

Reputation: 25

Fixed code thanks to help from @Tom

Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
    For i = 2 To UBound(ar, 1)
       .Item(ar(i, 1)) = Empty
    Next

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
    If .exists(ar(i, 1)) Then
       n = n + 1
            For j = 1 To UBound(ar, 2)
                   ar(n, j) = ar(i, j)
           Next j
   End If
Next i
End With
Sheet3.Cells(10, 8).Resize(n, UBound(ar, 2)).Value = ar
End Sub```

Upvotes: 1

Related Questions