VBA Excel比较两个列表的匹配，在单独的工作表上输出结果 [英] VBA Excel Compare Two Lists for Matches, Output Results on Separate Sheet

问题描述

以下是情况：

我正在验证数据库中的机构数据。每个机构都有一个与之相关的代码。我想确保我们的数据库中的代码是正确的，并检查我们的数据库中的名称没有错误。我将手动执行此操作，但有大约1200个记录要检查。

I am verifying institution data in a database. Each institution has a code associated with it. I want to be sure that the codes in our database are correct and also check that the names in our database contain no errors. I would do this manually, but there are some 1200 records to check.

我的所有数据都包含在单个工作簿中。

All my data is contained in a single workbook.

• 第一张表是我们数据库中的信息。机构名称在列B中，代码在列K中。代码存储为数字。


• 第二个工作表是最新信息。


• 第三个工作表是为结果设置的

我想将第一张表中的代码与第二张表中的代码进行匹配，然后在第三张表上输出结果。我想让每个代码有一行包含：机构名称从表1，代码和机构名称从第二个表。我想这样做，以便我可以检查代码是有效的，我们的数据库中的代码对应正确的机构，最后，所以我可以检查机构名称格式化和其他差异。

I want to match codes in the first sheet with codes in the second sheet, then output the results on the third sheet. I want for each code to have a row containing: the institution name from sheet 1, the code, and the institution name from the second sheet. I want to do this so that I can check that the code is valid, that the code in our database corresponds to the correct institution, and finally so I can check the institution name for formatting and other discrepancies.

这里是我的问题：

1. 而不是使用原始工作簿损坏的数据，我创建了一个副本来测试代码。我写了一个在测试文件中工作的程序，但是当我将它复制到原始，做任何适当的更改，代码不再工作。 为什么会是这样？


2. 由于复制代码似乎产生了一些问题，我将代码逐字地重新输入原始文件。有效。但是，只有一种。代码运行，但只会执行条件中包含的部分代码。它将执行块中的第一行，但是然后跳过其余的，然后再次不再输入条件。 由于一切都正确缩进，可能会导致这种情况发生？


3. 为了尝试检测为什么所有这些问题发生，使用F8。它逐步通过，但从不输入条件。它只是遍历循环。 使用Step Into（F8）命令时，它是否不评估IF语句？


4. 要测试我的循环是否正常运行，记录并且必须等待，我将更改FOR循环条件从1170到11.除了循环运行的次数，没有任何更改。然而，程序不再执行。当我改变数字回来，它的工作。 为什么会是这样？

1. Rather than use the original workbook and somehow corrupt the data, I created a copy to test the code on. I wrote a program which works in the test file, but when I copy it over to the original, making any appropriate changes, the code no longer works. Why might this be?
2. Since copying the code seemed to create some problem, I retyped the code, verbatim, into the original file. It worked. But, only sort of. The code ran, but would only execute part of the code contained in the conditional. It would execute the first line in the block, but then skip the rest and then never enter the conditional again. Since everything is indented properly, what could cause this to happen?
3. To try to detect why all of these problems occur, I've tried stepping through the program using F8. It steps through, but never enters the conditional. It just traverses the loops. When using the Step Into (F8) command, does it not evaluate IF statements?
4. To test if my loops are functioning properly, rather than run through ALL the records and have to wait, I will change the FOR loop condition from 1170 to 11. Nothing has changed except the number of times the loop runs. Yet, the program no longer executes. When I change the number back, it works. Why might this be?

这是我的代码。它可能不是最佳的，但我只需要运行比较ONCE。即使它花了2个小时，这比手工检查它们更少的工作。我知道这个代码工作，因为我得到它的工作在测试文件。

Here is my code. It may not be optimal, but I only need to run the comparison ONCE. Even if it took 2 hours, it would be less work than checking them all manually. I know this code works, because I got it to work in the test file. But why doesn't it work in the original and what can I do to make it work?

Sub FindMatches()

    Dim oldRow As Integer
    Dim newRow As Integer
    Dim i As Integer

    i = 2

    For oldRow = 2 To 1170
        For newRow = 2 To 1170
            If Worksheets("Sheet1").Cells(oldRow, 9) = Worksheets("Sheet2").Cells(newRow, 4) Then

                Worksheets("Sheet3").Cells(i, 1) = Worksheets("Sheet1").Cells(oldRow, 2)
                Worksheets("Sheet3").Cells(i, 2) = Worksheets("Sheet1").Cells(oldRow, 9)
                Worksheets("Sheet3").Cells(i, 3) = Worksheets("Sheet2").Cells(newRow, 1)

                i = i + 1

                Exit For
            End If
        Next newRow
    Next oldRow

End Sub

推荐答案

我认为你引用的列错误。列K是11不是9

I think you are referencing the wrong column. Column K is 11 not 9

这篇关于VBA Excel比较两个列表的匹配，在单独的工作表上输出结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！