excel宏·终极版

最新推荐文章于 2024-07-14 16:32:48 发布

huyu20122

最新推荐文章于 2024-07-14 16:32:48 发布

阅读量331

点赞数

分类专栏： VBA 文章标签： vba excel 宏

本文链接：https://blog.csdn.net/huyu20122/article/details/41721395

版权

Private Sub CommandButton1_Click()
'Sheet1=Q20.list;Sheet2=Q20trim.list;Sheet3=源数据表_副本
'新建Sheet4=批量合成+删除空行+变成M
'最终数据存于Sheet3列PQR

'批量合成
Dim s As Integer
s = TextBox1.Text
For i = 1 To s
    Sheet4.Cells(i, 1) = Mid(Sheet1.Cells(i, 1), 5, 5)
    Sheet4.Cells(i, 4) = Mid(Sheet2.Cells(i, 1), 5, 5)
    Sheet4.Cells(i, 2) = Sheet1.Cells(i, 2)
    Sheet4.Cells(i, 5) = Sheet2.Cells(i, 2)
Next
'合并计算
For i = 1 To s
    For j = 1 To i - 1
        If Sheet4.Cells(j, 1) = Sheet4.Cells(i, 1) Then
            Sheet4.Cells(i, 2) = Sheet4.Cells(i, 2) + Sheet4.Cells(j, 2)
            Sheet4.Cells(j, 1) = ""
            Sheet4.Cells(j, 2) = ""
        End If
    Next
    For j = i + 1 To s
        If Sheet4.Cells(j, 1) = Sheet4.Cells(i, 1) Then
            Sheet4.Cells(i, 2) = Sheet4.Cells(i, 2) + Sheet4.Cells(j, 2)
            Sheet4.Cells(j,