比较两个不同Excel表和工作簿中的列，然后将匹配值发布到另一列 [英] Comparing columns in two different excel sheets and workbooks, then posting the matching value into another column

问题描述

我有很多麻烦，想弄清楚这个宏，所以现在我在工作簿中使用2张;我想使用两种不同的工作簿，但并不像问题那么重要。

I'm having lots of trouble trying to figure out this macro, so right now I'm using 2 sheets in a workbook; I'd like to use two different workbooks but that isn't as important as the issue.

要解释，Sheet1列E是ID号，Sheet2中的列A是id号，现在Sheet1 E中的任何值与Sheet2中的列A中的值相匹配，我需要宏从Sheet 2列D复制相应的单元格。所以基本上如果E5（从表1）匹配Sheet2中的A1，我需要宏将Sheet2的D1输出到Sheet1的F5。如果Sheet1列E与Sheet 2列A中的值不匹配，则删除Sheet1列F的值也很好。

To explain, Sheet1 column E is the id number and in Sheet2 column A is the id number, now if any value in Sheet1 E matches a value in column A in Sheet2, I need the macro to copy the corresponding cell from Sheet 2 column D. So essentially if E5 (from sheet 1) matched A1 in Sheet2, I would need the macro to output Sheet2's D1 to Sheet1's F5. It would also be nice to delete the values of Sheet1 column F if Sheet1 column E doesn't match a value in Sheet 2 column A.

我有一些代码，但是如果Sheet1列E的值与Sheet2列A中的值相匹配，则从Sheet2的列D中粘贴值。问题是当从Sheet2的粘贴的Sheet粘贴到Sheet1的列F中时，该值与正确的值不匹配它与Sheet2相匹配。他们只是倾倒。所以如果Sheet1列E是这样的

I have some code but it just pastes the values from Sheet2's column D if a value of Sheet1 column E matches a value from Sheet2 column A. The problem is when the values pasted from Sheet2 are pasted in Sheet1's column F and the values aren't matched with the correct value that it matched with in Sheet2. They are just dumbed. So if Sheet1 column E was like this

Sheet1 Column E    Sheet1 F

1317               relays_120x120.jpg
1319              Control%20boards_120x120
1320              Control%20boards_120x120

Sheet2 Column A             Sheet2 column D
1317                       relays_120x120
1318                       /relays_120x120
1319                    ebay/SingleRunOval

但实际上，我需要它们都相等，如果Sheet1列E的值不在Sheet2列，然后不要在Sheet1列F中发布一个链接留空。

But in reality I need them all to be equal and if Sheet1 column E has a value that isnt in Sheet2 column, then dont post a link in Sheet1 column F leave it blank.

这是我有的代码

Sub FindMatches()

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

    i = 1

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

                Worksheets("Sheet1").Cells(i, 6) = Worksheets("Sheet2").Cells(oldRow, 4)


                i = i + 1

                Exit For
            End If
        Next newRow
    Next oldRow


End Sub

推荐答案

听起来像你可以用VLookup功能完成你的目标。将此公式添加到Sheet1 F1： = IFERROR（VLookup（E1，Sheet2！A：D，4，FALSE），）

Sounds like you could accomplish your goal with the VLookup function. Add this formula to Sheet1 F1: =IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")

如果找到匹配项，该公式会将单元格复制到sheet1中，但如果找不到匹配项，则单元格将保持空白。

That formula will copy the cell into sheet1 if a match is found, but if no match is found the cell will remain blank.

这篇关于比较两个不同Excel表和工作簿中的列，然后将匹配值发布到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！