How to compare columns from two different excel workbooks

Question

I have an Excel-Workbook which has two sheets 'Task' and 'Cities'. I need to compare the code for validation of records from 'Task' sheet with that one in 'Cities' Sheet.

I am able to do it as follows:

Dim CityString As String
Dim CityArray() As String

'Get the last row
'Dim lastRow As Integer
LastRow = Sheets("Task").UsedRange.Rows.Count
nLastRowSheet2 = Sheets("Cities").UsedRange.Rows.Count

Dim c As Range
Dim d As Range
Dim e As Variant

'Turn screen updating off to speed up macro code.
'User won't be able to see what the macro is doing, but it will run faster.
Application.ScreenUpdating = False

For Each c In Worksheets("Task").Range("A2:A" & LastRow)
    CityString = c
    CityArray() = Split(CityString, ";")
    For Each e In CityArray()
        e = Trim(e)

        Dim rngFnder As Range
        On Error Resume Next

            Set rngFnder = Sheets("Cities").Range("A2:A" & nLastRowSheet2).Find(e)

            If rngFnder Is Nothing Then
                c.Interior.Color = vbRed
            End If

        On Error GoTo 0
    Next
Next

Now I have another requirement where I have to do the same from two different workbooks.

('Task' and 'Cities' sheets are on two different Workbooks)

Can anyone tell me; what all changes I have to make to the above code?

score 2 · Accepted Answer · answered Jan 23 '15 at 10:44

When you say "on two different excelssheets" I'm guessing you mean they are in a different Excel workbook file?

When you use the following line you are refering to the sheet "Task" in the active workbook.

Worksheets("Task").Range("A2:A" & LastRow)

You should specify the workbook to avoid writing to the wrong one. Using thisWorkbook refers to the workbook with the code e.g.

ThisWorkbook.Worksheets("Task").Range("A2:A" & LastRow)

To access any open workbook including the current workbook use

Workbooks("example.xls").Worksheets("Task").Range("A2:A" & LastRow)

If the workbook is closed you can open it using

dim wrk as Workbook
set wrk = Workbooks.Open("C:\Docs\example.xls") 
wrk.Worksheets("Task").Range("A2:A" & LastRow)

If you want to know more about workbooks in VBA you can read about them here

Tushar · Answer 2 · 2015-01-23T11:51:03.027

0

I have prepared another workbook with name 'Sample_Data.xlsx' under which I will have a sheet 'cities'.

So where else in the below code I have to make changes to work it correctly; when my 'Sample_Data.xlsx' workbook is closed. The below code opens the workbook; but do not do validation.

Dim CityString As String
Dim CityArray() As String

'Get the last row
'Dim lastRow As Integer
LastRow = Sheets("Task").UsedRange.Rows.Count

Dim wrk As Workbook
Set wrk = Workbooks.Open("E:\Final\Sample_Data.xlsx")

nLastRowSheet2 = wrk.Worksheets("cities").UsedRange.Rows.Count

Dim c As Range
Dim d As Range
Dim e As Variant

'Turn screen updating off to speed up macro code.
'User won't be able to see what the macro is doing, but it will run faster.
Application.ScreenUpdating = False

For Each c In Worksheets("Task").Range("A2:A" & LastRow)
    CityString = c
    CityArray() = Split(CityString, ";")
    For Each e In CityArray()
        e = Trim(e)

        Dim rngFnder As Range
        On Error Resume Next

        Set rngFnder = wrk.Sheets("Cities").Range("A2:A" & nLastRowSheet2).Find(e)

        If rngFnder Is Nothing Then
            c.Interior.Color = vbRed
        End If

        On Error GoTo 0
    Next
Next

edited Jan 23 '15 at 11:51

answered Jan 23 '15 at 11:43

Tushar

3,527
9
27
49

In the "For Each" line you need to use wrk.Worksheets("Task") instead of Worksheets("Task"). The first refers to the workbook you opened, the second refers to whatever workbook is currently active. – Paul Kelly Jan 23 '15 at 11:56
which line is failing? if you remove on error resume next to see the error and give a little bit more information here? – Dubison Jan 23 '15 at 12:04
@PaulKelly Buddy; It will not work. As am comparing records from 'Task' sheet with 'Cities' sheet. 'Task' is my active sheet. So I don't have to do any changes wherever am applying loop for Range is Task sheet. Right? – Tushar Jan 23 '15 at 12:14
@Dubison Bro.. By the way am newbie in vba. After removing 'on error resume next' There is no change. (1)The Code from original post works fine (2) When I compare two columns from two different open workbooks; code works fine. (3) The code in **'my answer'** which is meant for validating the data when 'Sample_Data' workbook is closed; do not work. I don't know where I have to do more changes. – Tushar Jan 23 '15 at 12:19
When you open a workbook it will become the active one. – Paul Kelly Jan 23 '15 at 12:45
When you open a workbook it becomes the active one. Replace Worksheets("Task") with Workbooks("WorkbookName").Worksheets("Task"). This means you will refer to the correct sheet no matter which workbook is active,. – Paul Kelly Jan 23 '15 at 12:49
@PaulKelly Buddy, I guess am not able to convey you my point. My active workbook is 'Task' sheet. am going to open 'Sample_Data' workbook. In this case; what changes I have to make to above code to work it correctly same as other two codes ? – Tushar Jan 23 '15 at 12:54
@PaulKelly Ohk.. Just checked ur comment.. :p Let me check whether it works :) Thanks :) – Tushar Jan 23 '15 at 12:55

How to compare columns from two different excel workbooks

2 Answers2

Linked