Error Type mismatch when trying compare two ranges
Hi Friends
I'm trying to compare two ranges from different sheets:
UniqueItems
AllItems
but when I run the following code I get error message in IF condition (type mismatch)
Code:
Private Sub CommandButton1_Click()
With Sheets("UniqueItems")
.Range("A1").Select
.Range("G2:G65536").ClearContents
.Range("I1").ClearContents
End With
Dim i, j As Long
Dim Count, CountOrig As Integer
Dim LastRowAllItems As Long
Dim LastRowUniqueItems As Long
LastRowAllItems = Sheets("Allitems").Range("A65536").End(xlUp).Row
LastRowUniqueItems = Sheets("UniqueItems").Range("A65536").End(xlUp).Row
Count = 0
Sheets("UniqueItems").Range("I1") = "Processing ...."
For i = 2 To LastRowUniqueItems
For j = 2 To LastRowAllItems
If Sheets("UniqueItems").Range("A" & i & ":F" & i & "").Value = Sheets("AllItems").Range("A" & j & ":F" & j & "").Value Then
Count = Count + 1
Sheets("UniqueItems").Cells(i, "G") = Count
End If
Next j
Next i
MsgBox "Counting Items has been done successfully"
End Sub
Re: Error Type mismatch when trying compare two ranges
Yes, that is the problem I get as well, but what are you trying to compare? Are you trying to see if every cell in columns A through F on one sheet are the same as A through F on the other sheet?
Re: Error Type mismatch when trying compare two ranges
Thank you so much for helping
Yes I am
I was trying to see if every cell in columns A through F on one sheet are the same as A through F on the other sheet and counting if the code find out the range between A and F on sheet X equal any same on sheet Y.
Re: Error Type mismatch when trying compare two ranges
Ok, step through the code in the attached and see if it gets you close. I only used three columns (not 6), and looked for matches in the two sheets one cell at a time: