基于匹配的ID列替换数据的VBA宏

Sub ReplaceData()
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim SourceRange As Range
    Dim TargetRange As Range
    Dim IDColumn As Range
    Dim DataColumn As Range
    Dim IDCell As Range
    Dim ID As String
    Dim NewData As String
    
    ' 设置源数据表格和目标数据表格
    Set SourceSheet = ThisWorkbook.Sheets("SourceSheet")
    Set TargetSheet = ThisWorkbook.Sheets("TargetSheet")
    
    ' 设置源数据和目标数据的范围
    Set SourceRange = SourceSheet.Range("A1:B10")  ' 假设源数据范围为A1:B10
    Set TargetRange = TargetSheet.Range("A1:B10")  ' 假设目标数据范围为A1:B10
    
    ' 设置ID列和数据列
    Set IDColumn = SourceRange.Columns(1)
    Set DataColumn = SourceRange.Columns(2)
    
    ' 遍历目标数据的ID列
    For Each IDCell In TargetRange.Columns(1).Cells
        ID = IDCell.Value  ' 获取ID值
        
        ' 在源数据的ID列中查找匹配的ID
        Set FoundID = IDColumn.Find(ID, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not FoundID Is Nothing Then  ' 如果找到匹配的ID
            ' 获取新的数据值
            NewData = FoundID.Offset(0, 1).Value  ' 假设新的数据在源数据中的偏移量为1列
            
            ' 在目标数据中替换为新的数据值
            IDCell.Offset(0, 1).Value = NewData  ' 假设目标数据中的偏移量为1列
        End If
    Next IDCell
End Sub