VBA:如何比较两个不同工作表中的两个列

本文介绍了VBA:如何比较两个不同工作表中的两个列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.