Comparing cells with similar/different values

Question

I have problem in comparing cells with different value but it is working if the value compared is the same. The problem that i am facing is to compare between cell A1:A100 in worksheet1 and cell B3:B1000 in worksheet 2. If the value in worksheet1 and worksheet2 is equal it will be pasted to cell A in worksheet3 which i am able to do. If the value is not equal it will be pasted to cell B in worksheet3 this is the problem that I am facing.This is my code.

Private Sub CommandButton1_Click()

Dim val1, val2 As String

For i = 1 To 100

val1 = Worksheets("Sheet1").Cells(i, 1)

 For j = 3 To 1000
 val2 = Worksheets("Sheet2").Cells(j, 2)

  If (val1 = val2) Then
   Worksheets("Sheet3").Cells(i, 1) = val2

  ElseIf (val1 <> val2) Then
   Worksheets("Sheet3").Cells(i, 2) = val2

  End If

  Next

nested for loop is use to define the cell example for i= 1 to 100 is (A1:A100) — user2766881
What actually happens in your code is that it keeps comparing column B of Sheet 2 to cells(1,1), cells(2,1)....to cells(100,1) of Sheet 1 — sam092
When similar value from sheet 1 and 2 is found it is pasted to sheet 3 cell A. But if value from sheet 1 and 2 is not the same i am taking value from sheet 2 that is different from sheet 1 and paste it to sheet 3 cell B. This code does not track down the value that is not equal. Instead it paste the last value from sheet 2 to sheet 3 in column B sheet 3 starting from B1:B100 . The value that is the same is working only the value that is not the same. — user2766881
Do you have any suggestion to improve on my code so that value from sheet 2 that is different from sheet 1 will be pasted to sheet 3 cell B? — user2766881

sam092 sam092 · Accepted Answer · 2013-10-03T03:33:11

You actually mean ....."for every value in Column B in Sheet 2, if this is found within the range A1:A100"...

Finally got you. See if this helps.

Sub test()
    Dim val As Variant
    Dim found As Integer, notFound As Integer

    found = 0
    notFound = 0

    For j = 3 To 1000
        val = Worksheets("Sheet2").Cells(j, 2).Value
        If Not IsError(Application.Match(val, Worksheets("Sheet1").Range("A1:A100"), 0)) Then
            'Comment this line if you don't want to paste the values that are the same
            'Worksheets("Sheet3").Range("A1").Offset(found) = val
            found = found + 1
        Else
            Worksheets("Sheet3").Range("B1").Offset(notFound) = val
            notFound = notFound + 1
        End If
    Next
End Sub

Comparing cells with similar/different values

1 Answers