Results 1 to 6 of 6

Thread: Error Type mismatch when trying compare two ranges

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    Error Type mismatch when trying compare two ranges

    Hi Friends

    I'm trying to compare two ranges from different sheets:

    1. UniqueItems
    2. 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
    Can anyone help to explain?

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Error Type mismatch when trying compare two ranges

    What about these two ranges are you trying to compare exactly?

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    Re: Error Type mismatch when trying compare two ranges

    The code dose not go inside if condition and give error message.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    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.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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:
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width