备忘：一个自己写过的简单的EXCEL VBA程序

来源：互联网发布：身份证照片检测软件编辑：程序博客网时间：2024/05/19 06:39

Public Sub tongji()
    Dim x As Range
    Dim y As Range
    Dim z1 As Range
    Dim z2 As Range
    Dim sum_in As Integer
    Dim sum_out As Integer

    Sheet3.Range("A1").Value = "进货名称"
    Sheet3.Range("B1").Value = "进货总量"
    Sheet3.Range("C1").Value = "销售总量"
    Sheet3.Range("D1").Value = "库存总量"

    Set z1 = Sheet3.Range("A2")  '对象赋值要用set
    Set z2 = Sheet3.Range("C2")
    '进货总量统计
    For Each x In Sheet1.Range("J17:J45")   '对于每一种商品
        sum_in = 0
        For Each y In Sheet1.Range("D4:D41") '遍历这种商品的每一次进货，统计进货总量
            If y.Value = x.Value Then
                sum_in = sum_in + y.Offset(0, 2).Value
            End If
        Next
        z1.Value = x.Value
        z1.Offset(0, 1).Value = sum_in
        Set z1 = z1.Offset(1, 0)

    '销售总量统计
        sum_out = 0
        For Each y In Sheet2.Range("G4:G100")
            If y.Value = x.Value Then
                sum_out = sum_out + y.Offset(0, 2).Value
            End If
        Next
        z2.Value = sum_out
        z2.Offset(0, 1).Value = sum_in - sum_out '库存量统计
        Set z2 = z2.Offset(1, 0)
   Next

End Sub