CODEWITHSUNDEEP
vbacompare
user2766881
user2766881

Reputation: 19

Comparing cells with similar/different values

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

Next

End Sub

Upvotes: 0

Views: 855

Answers (1)

sam092
sam092

Reputation: 1335

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

Upvotes: 0

Related Questions