以下是一个使用VBA在Excel中将Sheet1中的三个表格按照id进行合并并输出到Sheet2的示例代码:

Sub MergeTables()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim rng1 As Range, rng2 As Range, cell As Range
    
    ' 设置工作表对象
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' 获取Sheet1中表格的最后一行
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    
    ' 设置Sheet2的起始行
    lastRow2 = 1
    
    ' 遍历Sheet1中的每个表格
    For Each rng1 In ws1.Range("A1:C" & lastRow1).SpecialCells(xlCellTypeConstants).Areas
        ' 遍历Sheet2中已合并的数据,找到最后一行
        lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1
        
        ' 遍历当前表格的每个单元格
        For Each cell In rng1
            ' 查找Sheet2中是否存在相同的id
            Set rng2 = ws2.Range("A1:A" & lastRow2).Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
            
            ' 如果找到了相同的id,则将当前表格的数据复制到Sheet2的相应行
            If Not rng2 Is Nothing Then
                cell.Resize(, 3).Copy ws2.Cells(rng2.Row, "B")
            Else
                ' 如果没有找到相同的id,则将当前表格的数据复制到Sheet2的下一行
                cell.Resize(, 3).Copy ws2.Cells(lastRow2, "B")
                lastRow2 = lastRow2 + 1
            End If
        Next cell
    Next rng1
    
    MsgBox "合并完毕!", vbInformation
End Sub

在VBA编辑器中,将代码粘贴到一个新的模块中,然后运行MergeTables过程即可实现将Sheet1中的三个表格按照id进行合并并输出到Sheet2

excel 有sheet1中有三个表格把三个表格合并起来。合并条件是id=id并输出到sheet2vba实现

原文地址: https://www.cveoy.top/t/topic/iqYq 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录