excel 有sheet1中有三个表格把三个表格合并起来。合并条件是id=id并输出到sheet2vba实现
以下是一个使用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
原文地址: https://www.cveoy.top/t/topic/iqYq 著作权归作者所有。请勿转载和采集!