VBA: How to compare two columns in two different worksheets

Question

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

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.

Can anyone help?

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

Answer 1

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

Take a look how to refer to the worksheets:

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

As you see the methods are mainly 2:

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

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,..

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

---

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.

• Fiops: Works perfectly now. Thank you! :)