Excel宏可以比较2张纸上的数据并在匹配时粘贴值 [英] Excel Macro to compare data from 2 sheets and paste values over on match

问题描述

我有2个工作表，我想比较一下工作表1中的名字和姓氏(A和B列)中的数据是否在工作表2中(也包括A和B列)，其中名字在列中B列中的A和姓氏.如果在sheet2中找到了sheet1的名字和姓氏，并且如果该行的工作表2(E列)中的ID号等于2，那么我想复制该客户从工作表2到工作表1的P列的明细行(D列).

I have 2 sheets, I want to compare if the data in sheet 1 for first name and last name (Column A and B) are found in sheet 2 (Also Column A and B), where the first name is in column A and Last Name in column B. If the first and last name from sheet1 are found within sheet2, and also if the ID number in sheet 2 (column E) for that row is equal to 2, then I want to copy over the customer detail row (Column D) from sheet2 into column P of Sheet 1.

我觉得下面的代码应该可以工作，但是对我来说，它并没有将客户数据粘贴到sheet1的P列中.有什么想法吗?

I feel like the code I have below should work, but it isn't pasting the customer data into column P of sheet1 for me. Any ideas why?

Sub matchandpaste()


Dim cell, cell2, revenue As Range
Dim wbk As Workbook
Dim sheet1, sheet2 As Worksheet
Dim temp, firstName, lastName As String

Set wbk = ThisWorkbook

Set Sheet1 = wbk.Sheets("Sheet1")
Set sheet2 = wbk.Sheets("Sheet2")


For Each cell In Sheet1.Range("B2:B" & Range("B2").End(xlDown).Row)
    lastName = cell.Value
    firstName = cell.Offset(0, -1).Value
    Set revenue = cell.Offset(0, 14)
    For Each cell2 In sheet2.Range("B2:B" & Range("B2").End(xlDown).Row)
        If lastName = cell2.Value And firstName = cell2.Offset(0, -1).Value And cell2.Offset(0, 3).Value = 2 Then
            revenue = cell2.Offset(0, 2).Value
        End If
    Next cell2
Next cell

推荐答案

您只需在 revenue 范围变量的末尾放置一个 .value .像这样:

You just have to put a .value at the end of the revenue range variable. Like this:

Sub matchandpaste()


Dim cell, cell2, revenue As Range
Dim wbk As Workbook
Dim sheet1, sheet2 As Worksheet
Dim temp, firstName, lastName As String

Set wbk = ThisWorkbook

Set Sheet1 = wbk.Sheets("Sheet1")
Set sheet2 = wbk.Sheets("Sheet2")


For Each cell In Sheet1.Range("B2:B" & Range("B2").End(xlDown).Row)
    lastName = cell.Value
    firstName = cell.Offset(0, -1).Value
    Set revenue = cell.Offset(0, 14)
    For Each cell2 In sheet2.Range("B2:B" & Range("B2").End(xlDown).Row)
        If lastName = cell2.Value And firstName = cell2.Offset(0, -1).Value And cell2.Offset(0, 3).Value = 2 Then
            revenue.value = cell2.Offset(0, 2).Value
        End If
    Next cell2

Next cell

End Sub

这篇关于Excel宏可以比较2张纸上的数据并在匹配时粘贴值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！