vba实现分类汇总功能

Option Explicit
Sub test()
    Dim sht As Worksheet, rng As Range
    Set sht = ActiveSheet
    Dim i As Integer, index As Integer, subtotal As Double, dm As Variant
    Dim oldIndex As Integer, oldSubtotal As Double
    
    Dim startLine As Integer
    Dim lastLine As Integer
    
    startLine = 2
    lastLine = ActiveCell.CurrentRegion.Rows.Count
    
    dm = sht.Cells(lastLine, 2).Value
    subtotal = sht.Cells(lastLine, 3).Value
    index = lastLine
    For i = lastLine - 1 To startLine Step -1
        If dm <> sht.Cells(i, 2).Value Then
            dm = sht.Cells(i, 2).Value

            oldIndex = index
            oldSubtotal = subtotal

            index = i
            subtotal = sht.Cells(index, 3).Value

            sht.Cells(oldIndex + 1, 2).EntireRow.Insert
            sht.Cells(oldIndex + 1, 2).Value = "小计"
            sht.Cells(oldIndex + 1, 3).Value = oldSubtotal
        Else
            subtotal = subtotal + sht.Cells(i, 3).Value
        End If
        
        If i = startLine Then
            sht.Cells(index + 1, 2).EntireRow.Insert
            sht.Cells(index + 1, 2).Value = "小计"
            sht.Cells(index + 1, 3).Value = subtotal
       
        End If
        
    Next
    
End Sub

 

发布了117 篇原创文章 · 获赞 7 · 访问量 4万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览