Excel VBA比较列数据复制行

Okay so I managed to work out this code somehow with help from many of the coding experts here. I need to create a macro that compares data in two worksheets.

好的,所以我设法在这里得到了许多编码专家的帮助。我需要创建一个宏来比较两个工作表中的数据。

In both of my worksheets, there is a column named "eRequest ID", I have to copy the rows of records that DO NOT have an "eRequest ID" in BOTH FILES.

在我的两个工作表中,都有一个名为“eRequest ID”的列,我必须在两个文件中复制没有“eRequest ID”的记录行。

The code i worked out now copies recrods that have an "eRequest ID" in EITHER FILES. So logically speaking i have to "negate" the IF condition in my code below, but I have no idea how to do it as I'm a total beginner at coding, VBA included.

我制定的代码现在复制了在EITHER FILES中具有“eRequest ID”的recrods。从逻辑上讲,我必须在下面的代码中“否定”IF条件,但我不知道如何做,因为我是编码的初学者,包括VBA。

 Sub compareAndCopy()
   Dim lastRowE As Integer
   Dim lastRowF As Integer
   Dim lastRowM As Integer
   Dim foundTrue As Boolean

    Application.ScreenUpdating = False

    lastRowE = Sheets("JULY15Release_Master Inventory").Cells(Sheets("JULY15Release_Master Inventory").Rows.Count, "A").End(xlUp).Row
    lastRowF = Sheets("JULY15Release_Dev status").Cells(Sheets("JULY15Release_Dev status").Rows.Count, "A").End(xlUp).Row
    lastRowM = Sheets("Mismatch").Cells(Sheets("Mismatch").Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastRowE
    foundTrue = False
    For j = 1 To lastRowF

    If Sheets("JULY15Release_Master Inventory").Cells(i, 2).Value = Sheets   ("JULY15Release_Dev status").Cells(j, 7).Value Then
        foundTrue = False
        Exit For
    End If

    Next j

    If Not foundTrue Then
    Sheets("JULY15Release_Master Inventory").Rows(i).Copy Destination:= _
    Sheets("Mismatch").Rows(lastRowM + 1)
    lastRowM = lastRowM + 1

    End If


    Next i

    Application.ScreenUpdating = False

    End Sub

Forgive my poor formatting.. I'm very new to * as well.

原谅我糟糕的格式化..我也是*的新手。

One more thing, I just realized that this code only copies rows from Sheets("JULY15Release_Master Inventory") It doesnt copy rows from Sheets("JULY15Release_Dev status") even if there is only one "eRequest ID" for that data.

还有一点,我刚刚意识到这段代码只复制了Sheets中的行(“JULY15Release_Master Inventory”)它不会复制Sheets中的行(“JULY15Release_Dev status”),即使该数据只有一个“eRequest ID”。

1 个解决方案

#1

Your "eRequest ID" are located in both sheets in column A so I compare this column. If there is a match in both sheets the loop exists. If there is no match then the row get copied to "mismatch". loop for both sheets.

您的“eRequest ID”位于A列的两个工作表中,因此我对此列进行了比较。如果两个工作表中都存在匹配,则存在循环。如果没有匹配,则该行被复制为“不匹配”。两张纸的循环。

Sub compareAndCopy()
   Dim lastRowMaster As Integer
   Dim lastRowDev As Integer
   Dim lastRowMis As Integer
   Dim foundTrue As Boolean

    Application.ScreenUpdating = False

    lastRowMaster = Sheets("JULY15Release_Master Inventory").Cells(Sheets("JULY15Release_Master Inventory").Rows.Count, "A").End(xlUp).Row
    lastRowDev = Sheets("JULY15Release_Dev status").Cells(Sheets("JULY15Release_Dev status").Rows.Count, "A").End(xlUp).Row
    lastRowMis = Sheets("Mismatch").Cells(Sheets("Mismatch").Rows.Count, "A").End(xlUp).Row

    'start loop Master
    For i = 2 To lastRowMaster '1 = headers
    foundTrue = False
    For j = 2 To lastRowDev

    If Sheets("JULY15Release_Master Inventory").Cells(i, 1).Value = Sheets("JULY15Release_Dev status").Cells(j, 1).Value Then
        foundTrue = True
        Exit For
    End If

    Next j

    If foundTrue = False Then
    Sheets("JULY15Release_Master Inventory").Rows(i).Copy Destination:= _
    Sheets("Mismatch").Rows(lastRowMis + 1)
    lastRowMis = lastRowMis + 1

    End If

    Next i
    'end loop master

    Sheets("Mismatch").Cells(lastRowMis + 2, 1).Value = "results from Dev"
    lastRowMis = lastRowMis + 2

    'start loop Dev
    For i = 2 To lastRowDev '1 = headers
    foundTrue = False
    For j = 2 To lastRowMaster

    If Sheets("JULY15Release_Dev status").Cells(i, 1).Value = Sheets("JULY15Release_Master Inventory").Cells(i, 1).Value Then
        foundTrue = True
        Exit For
    End If

    Next j

    If foundTrue = False Then
    Sheets("JULY15Release_Dev status").Rows(i).Copy Destination:= _
    Sheets("Mismatch").Rows(lastRowMis + 1)
    lastRowMis = lastRowMis + 1

    End If

    Next i
    'end loop dev


    Application.ScreenUpdating = False

    End Sub