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