備忘：一個自己寫過的簡單的EXCEL VBA程式

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