Excel 2010宏来比较两列以查找匹配的值 [英] Excel 2010 Macro to compare two columns for finding the matched value

问题描述

我非常新的Excel宏和VBA，请帮助我请与我的下面的情况。
这是情况，我有两张（sheet1和sheet2）在sheet1中有两列名称和数字，在sheet2中，我的数字沿着其他信息，如日期，收费等。

I am very new to Excel macros and VBA, kindly help me please with my below situation. Here is the situation, i have two sheets (sheet1 and sheet 2) in sheet1 there is two columns name and number and in sheet2 i have numbers along its other information like date, charging and etc.

表1

否姓名PhoneNumber

No Name PhoneNumber

1 Bob 7254

1 Bob 7254

2 Cristin 5468

2 Cristin 5468

3 Luara 1234

3 Luara 1234

Sheet2

No PhoneNumber日期收费姓名

No PhoneNumber Date Charged Name

1 1145 12/30/2014 2 $

1 1145 12/30/2014 2$

2 7254 11/26/2014 3 $

2 7254 11/26/2014 3$

3 2365 3 / 9/2014 7 $

3 2365 3/9/2014 7$

4 5468 3/10/2014

4 5468 3/10/2014

5 1234 3/11/2014

5 1234 3/11/2014

我想要的是将sheet2（B列）的PhoneNumber列与sheet1（C列）的PhoneNumber列进行比较，如果找到匹配项，则复制名称（B列）从Sheet1到Shee的Name列t2（E列）。如果没有匹配，那么sheet2中的名称列必须为空。

What i want is to compare PhoneNumber column of sheet2 (B column) with PhoneNumber column of sheet1 (C column) and if a matching is found then copy Name (B column) from Sheet1 into Name column of sheet2 (E column). If no match then the name column in sheet2 must be blank.

我已经搜索并找到以下代码并修改了一点，但我不知道它是否正确或否：

i have searched and found the below code and modified a bit but i am not sure whether it is correct or no:

    Sub test()

    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
        For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
            Set rng1 = Sheets("Sheet2").Range("B" & i)
            For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
                Set rng2 = Sheets("Sheet1").Range("C" & j)
                If rng1.Value = rng2.Value Then
                    Range("B2:B" & TotalRows).Copy Destination:=Sheets("Sheet2").Range("E2")
                End If

            Set rng2 = Nothing
        Next j
        Set rng1 = Nothing
    Next i
End Sub

请帮助我，因为我的这个pr我会高度赞赏你在这方面的帮助。

Please help me as the time is so short for my this project and i will highly appreciate your assistance in this regard.

推荐答案

看起来你几乎在那里。然而，您的复制行需要一点调整。在下面的示例中，我添加了一个名为rngName的附加变量，用于存储要复制的名称的范围，并为其分配一个值。如果数字匹配（即rng1.value = rng2.value），它将将包含该名称的范围复制到表2中的关联行。请注意，对于复制范围，我使用.Range（E& i） 。您的示例中的复制范围将始终将名称放在与其始终分配给E2的单元格相同的单元格中。此外，您有一个名为TotalRows的变量没有值。它必须意味着原始代码中的东西，你复制它，所以我也摆脱了这一点。尝试一下，让我知道它对你有用。

Looks like you were almost there. Your copy line needed a little tweaking, however. In the example below, I added an additional variable called rngName, to store the range of the name to be copied and assigned it a value in the for j loop. if the numbers match (i.e. rng1.value = rng2.value) it will copy the range containing the name to the associated row in sheet 2. Notice that I used .Range ("E" & i) for the copy-to range. The copy-to range in your example would always drop the name in the same cell as its always assigned to "E2". Also, you have a variable called TotalRows that had no value. It must have meant something in the original code you copied it from, so I got rid of that too. Try this and let me know how it works for you.

Sub test()
 Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
    For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Sheet2").Range("B" & i)
        For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet1").Range("C" & j)
            Set rngName = Sheets("Sheet1").Range("B" & j)
            If rng1.Value = rng2.Value Then
                rngName.Copy Destination:=Worksheets("Sheet2").Range("E" & i)
            End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
 Next i
End Sub

这篇关于Excel 2010宏来比较两列以查找匹配的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！