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

最新推荐文章于 2024-04-15 22:30:04 发布

最新推荐文章于 2024-04-15 22:30:04 发布 · 86 阅读

本文介绍了一段使用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