Macro Run Time Error 13: Mismatch

jpedges38 · Apr 24th 2012

Hey, I am trying to use this code to check for duplicates in two columns, but I am getting a mismatch error whenever I try to run the macro:

Code

Sub Dups()
Dim a, i As Long, x As Long, rCell As Range
a = Sheets("Sheet1").Columns("A").SpecialCells(2, 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(a, 1)
        If Not .exists(Trim(a(i, 1))) Then
            x = x + 1
            .Item(Trim(a(i, 1))) = x
        End If
    Next
    For Each rCell In Sheets("Sheet1").Columns("B").SpecialCells(2, 2)
        If .exists(Trim(rCell.Value)) Then
            rCell.Offset(, 1) = Trim(rCell.Value)
        End If
    Next
End With
Set rCell = Nothing
End Sub

Display More

The error is coming somewhere in the For i = 1 To UBound(a, 1) I believe. If anyone could help me fix this error that would be great.

-J

dangle · Apr 25th 2012

Re: Macro Run Time Error 13: Mismatch

I think there must be some errors in the data itself.

Try:

Code

Sub Dups2()
Dim a, i As Long, x As Long, rCell As Range
a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(a, 1)
        On Error Resume Next
        If Not .exists(Trim(a(i, 1))) Then
            x = x + 1
            .Item(Trim(a(i, 1))) = x
        End If
        On Error GoTo 0
    Next
    For Each rCell In Range("B1", Range("B" & Rows.Count).End(xlUp))
        On Error Resume Next
        If .exists(Trim(rCell.Value)) Then
            rCell.Offset(, 1) = Trim(rCell.Value)
        End If
        On Error GoTo 0
    Next
End With
Set rCell = Nothing
End Sub

Display More

Macro Run Time Error 13: Mismatch

Participate now!

Share