VBA.EXCEL.批量

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

huyu20122

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

阅读量300

点赞数

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

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

版权

VBA 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

'批量合成
For i = 1 To 100
    Sheet4.Cells(i, 1) = Mid(Sheet1.Cells(i, 1), 5, 5)
    Sheet4.Cells(i, 2) = Sheet1.Cells(i, 2) + Sheet1.Cells(i + 1, 2)
    Sheet4.Cells(i, 3) = Sheet2.Cells(i, 2) + Sheet2.Cells(i + 1, 2)
    i = i + 1
Next
'删除空行
For i = 1 To 100
    If Sheet4.Cells(i, 1) = "" Then
        Sheet4.Rows(i).Delete
    End If
Next
'变成M
For i = 1 To 50
    Sheet4.Cells(i, 2) = Sheet4.Cells(i, 2) / 1000000
    Sheet4.Cells(i, 3) = Sheet4.Cells(i, 3) / 1000000
Next
'最后一步
Dim a, b As Integer
For i = 3 To 53
    a = Mid(Sheet3.Cells(i, 2), 5, 5)
    For b = 3 To 42
        If Sheet4.Cells(b, 1) = a Then
            Sheet3.Cells(i, 16) = Sheet4.Cells(b, 2)
            Sheet3.Cells(i, 16).NumberFormatLocal = "0.0_ "
            Sheet3.Cells(i, 17) = Sheet4.Cells(b, 3)
            Sheet3.Cells(i, 17).NumberFormatLocal = "0.0_ "
            Sheet3.Cells(i, 18) = Sheet3.Cells(i, 17) / Sheet3.Cells(i, 16)
            Sheet3.Cells(i, 18) = NumberFormatLocal = "0.0%"
        End If
    Next
Next
End Sub