CODEWITHSUNDEEP
vbaexcelexcel-formula
Fiops
Fiops

Reputation: 97

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.

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

Upvotes: 2

Views: 1321

Answers (1)

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 1

Related Questions