mercredi 23 mars 2016

Excel VBA Not Equal not working

Hi have found a macro on another forum that solved my problem: It copies cells from Sheet 2 matching Sheet 1.

If .Cells(i, 1) = WS.Cells(j, 1) And .Cells(i, 3) = WS.Cells(j, 3) And     .Cells(i, 4) = WS.Cells(j, 4) Then
                 ' If a match is found:
                    WS.Cells(j, 6) = .Cells(i, 1)
                      WS.Cells(j, 7) = .Cells(i, 2)
                        WS.Cells(j, 8) = .Cells(i, 3)
                          WS.Cells(j, 9) = .Cells(i, 4)

If I change the line to look for non matching records it enters them into Row K,L,M,N and then loops only through rows 2 and 3 and not down the spreadsheet creating the full list.

 If .Cells(i, 1) <> WS.Cells(j, 1) And .Cells(i, 3) <> WS.Cells(j, 3) And .Cells(i, 4) <> WS.Cells(j, 4) Then
                ' If a match is found:
                    WS.Cells(j, 11) = .Cells(i, 1)
                      WS.Cells(j, 12) = .Cells(i, 2)
                        WS.Cells(j, 13) = .Cells(i, 3)
                          WS.Cells(j, 14) = .Cells(i, 4)

Can anyone advise me on why this is please? Here is the full code:

Sub test2()

 Dim WS As Worksheet
    Set WS = Sheets("Master")

    Dim RowsMaster As Integer, Rows2 As Integer
    RowsMaster = WS.Cells(1048576, 1).End(xlUp).Row
    Rows2 = Worksheets(2).Cells(1048576, 1).End(xlUp).Row
    ' Get the number of used rows for each sheet

    With Worksheets(2)
        For i = 2 To Rows2
        ' Loop through Sheet 2
            For j = 2 To RowsMaster
            ' Loop through the Master sheet
                'below line checks for matches to variable number, file and field

    If .Cells(i, 1) <> WS.Cells(j, 1) And .Cells(i, 3) <> WS.Cells(j, 3) And .Cells(i, 4) <> WS.Cells(j, 4) Then
                ' If a match is found:
                    WS.Cells(j, 11) = .Cells(i, 1)
                      WS.Cells(j, 12) = .Cells(i, 2)
                        WS.Cells(j, 13) = .Cells(i, 3)
                          WS.Cells(j, 14) = .Cells(i, 4)

                    ' above copies in the data
                    Exit For
                    ' No point in continuing the search for that company
                ElseIf j = RowsMaster Then
                ' If we got to the end of the Master sheet
                ' and haven't found a company match
                    RowsMaster = RowsMaster + 1
                    ' Increment the number of rows
                    For k = 1 To 4 ' Change 3 to however many fields Sheet2 has
                        WS.Cells(RowsMaster, k) = .Cells(i, k)
                        ' Copy the data from Sheet2 in on the bottom row of Master
                    Next
                End If

            Next j
        Next i
      End With
End Sub

Aucun commentaire:

Enregistrer un commentaire