How can i copy non matching numbers in vba

Question

Im trying to compare two sheets which is sheet1 and sheet2 and print values in sheet3. When i am comparing between sheet1 and sheet2 and looking for non matching numbers, somehow my loop doesn't stop and lots of time its printing the samenumber. Here is my code but is there any other ways i can find non matching numbers between two sheets and paste it into sheet3.

 lastrow1 = Sheets("Sheet1").UsedRange.Row - 1 + Sheets("Sheet1").UsedRange.Rows.Count
 lastrow2 = Sheets("Sheet2").UsedRange.Row - 1 + Sheets("Sheet2").UsedRange.Rows.Count

  a = 1
  b = 1
  c = 1

For i = a To lastrow1
   For ii = b To lastrow2

     If Worksheets("Sheet1").Cells(i, 1) <> Worksheets("Sheet2").Cells(ii, 1) Then
        Worksheets("Sheet3").Range("A" & x) = Worksheets("Sheet1").Cells(i, 1)
        x = x + 1
     End If

   Next ii
Next i

should add a boolean type variable to this logic and copy only if it value is still false at the end of each outer loop iteration... — , Nov 06 '14 at 12:38
You should also never use `UsedRange`. check out @SiddhartRout 's answer on this question. http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920 — Goos van den Bekerom, Nov 06 '14 at 12:40

score 0 · Accepted Answer · answered Nov 06 '14 at 13:09

Note: the below code checks column A in Sheet1 against column A in Sheet2. Then, the Main() calls the same code with a reverse order so all the numbers in Sheet2 in column A are checked against Sheet1 column A. If you only want to see the values that are in Sheet1 but not in Sheet2 comment out the second call to PrintNonMatching in Main()

Sub Main()
    PrintNonMatching "Sheet1", "Sheet2", "Sheet3"
    PrintNonMatching "Sheet2", "Sheet1", "Sheet3"
End Sub

Sub PrintNonMatching(arg1 As String, arg2 As String, arg3 As String)

    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Set sh1 = Sheets(arg1): Set sh2 = Sheets(arg2): Set sh3 = Sheets(arg3)

    Dim match As Boolean
    For Each c1 In sh1.Range("A1:A" & sh1.Range("A" & Rows.Count).End(xlUp).Row)
        For Each c2 In sh2.Range("A1:A" & sh2.Range("A" & Rows.Count).End(xlUp).Row)
            If c1 = c2 Then match = True
        Next
        If Not match Then
            sh3.Range("A" & sh3.Range("A" & Rows.Count).End(xlUp).Row + 1) = c1
        End If
        match = False
    Next

End Sub

can you tell me what you mean by PrintNonMatching(arg1 As String, arg2 As String, arg3 As String) and did you use collection or what kind of method and Set sh1 = Sheets(arg1): Set sh2 = Sheets(arg2): Set sh3 = Sheets(arg3). It's working for me but can you please explain what's going on, im kinda lost. anyways thanks a lot @vba4all\ — lisa_rao007, Nov 06 '14 at 13:21
sure. `Sheets()` itself is an Excel's built-in collection of all Sheets. Passing the sheet name to it retrieves a reference to the sheet so instead of `Sheets("Sheet1")` you use `sh1` throughout the code - much more convenient. `PrintNonMatching()` is another procedure that takes parameter and does the job. Having the ability to pass arguments to a function makes it reusable under different circumstances - see [DRY Principle](http://en.wikipedia.org/wiki/Don't_repeat_yourself) — , Nov 06 '14 at 13:36

How can i copy non matching numbers in vba

1 Answers1