如何比较两个不同的Excel工作簿中的列 [英] How to compare columns from two different excel workbooks

问题描述

我有一本Excel工作簿，其中有两个工作表任务"和城市". 我需要将任务"表中用于验证记录的代码与城市"表中的代码进行比较.

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.

我能够做到如下:

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?

推荐答案

当您说在两个不同的excelssheets上"时，我猜您是说它们在不同的Excel工作簿文件中?

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)

您应指定工作簿，以避免写错工作簿.使用thisWorkbook指的是带有代码的工作簿，例如

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)

如果您想了解有关VBA中工作簿的更多信息，请阅读此处

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

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