VBA:如何比较两个不同工作表中的两个列 [英] VBA: How to compare two columns in two different worksheets

问题描述

只有在两列都在同一工作表中时，我才设法做到这一点.

I have only managed to do it when both columns are in the same worksheet.

我想做什么: 比较两个不同工作表(工作表A和B)中的两列.仅存在于工作表A中但不存在于工作表B中的单元格应粘贴到工作表B中.仅存在于工作表B中但不存在于工作表A中的单元格也是如此.我也想将类别的第一行留空.因此它应该从第二行开始计数.

What I want to do: Compare two columns in two different worksheets (worksheet A & B). Cells that only exist in worksheet A but not in worksheet B should be pasted in worksheet B. Same goes for cells that only exist in worksheet B but not in worksheet A. I would also like to have the first row empty for categories. So it should begin counting with the second row.

任何人都可以帮忙吗?

Sub test()

Dim d1 As Object, d2 As Object, d3 As Object, e

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")

For Each e In Cells(1).Resize(Cells(Rows.Count, 1).End(3).Row).Value
    d1(e) = True
    d2(e) = True
Next e

For Each e In Cells(2).Resize(Cells(Rows.Count, 2).End(3).Row).Value
    If (d2(e)) * (d1.exists(e)) Then d1.Remove e
    If Not d2(e) Then d3(e) = True
Next e

On Error Resume Next
Range("J1").Resize(d1.Count) = Application.Transpose(d1.keys)
Range("K1").Resize(d3.Count) = Application.Transpose(d3.keys)
On Error GoTo 0

End Sub

推荐答案

您必须将单元格，行等引用到给定的工作表.如果操作正确，这不是一项艰巨的任务.

You have to refer the cells, rows, and etc to a given worksheet. It is not a difficult task, if you do it correctly.

看看如何引用工作表:

Sub TestMe()

    Dim shA     As Worksheet
    Dim shB     As Worksheet
    Dim e       As Range

    Set shA = Worksheets(1)
    Set shB = Worksheets("Tabelle2")

    With shA    
        For Each e In .Cells(1).Resize(.Cells(.Rows.Count, 1).End(3).Row)
            Debug.Print e.Address
        Next e    
    End With    
End Sub

您看到的方法主要是2:

As you see the methods are mainly 2:

• 按索引-Set shA = Worksheets(1)
• 按名称-Set shB = Worksheets("Tabelle2")

• By index - Set shA = Worksheets(1)
• By name - Set shB = Worksheets("Tabelle2")

还有第三个方法，即vba的对象名，但是您现在可能不需要它了.在上面的示例中，请注意，您在此行中引用了3次父shA:

There is a third method, by vba's object name, but you probably not need it now. In the example above, pay attention that you are refering three times to the parent shA in this line:

For Each e In .Cells(1).Resize(.Cells(.Rows.Count, 1).End(3).Row)

• .Cells(1)...
• .(Cells(...
• .Rows.Count,..
• .Cells(1)...
• .(Cells(...
• .Rows.Count,..

这是非常重要的，当使用工作表并且可能是StackOverflow中的数字1错误时，人们在使用VBA时会犯错误.例如.此处-尝试访问时VBA运行时错误1004工作表范围或 VBA-运行此代码时出现运行时错误"1004"

This is rather important, when working with worksheets and probably the number 1 mistake in StackOverflow, that people do when working with VBA. E.g. here - VBA Runtime error 1004 when trying to access range of sheet or VBA - Getting a runtime error '1004' when running this code

如果您错过了三个时间参考，则上述范围内的Cells和Rows将自动引用为ActiveSheet.可能并非总是这样.

If you miss the three time-referring, the Cells and the Rows from the above range would be automatically referred to the ActiveSheet. Which is probably not always the desired case.

这篇关于VBA:如何比较两个不同工作表中的两个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！