最近帮朋友做了一个报表统计的excel,因为本人比较懒,就直接用vba写了逻辑,而没有使用公式,代码实现了一些较简单的功能,例:算总收入,总支出,按月份统计的报销额度,根据财务人员给的公式自动算出管理费等,在这里mark一下,语言不是最重要的,重要的是解决问题的思路,与君共勉。因为我只对sheet1做了宏处理,所以在代码中使用worksheets(1)来查找sheet1中的单元格。有需要也可以更改成对整个工作簿作用。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, j
i = 0
j = 0
inner = 0
outer = 0
Dim cost(1 To 12) As Long
Dim earn(1 To 12) As Long
For r = 2 To Worksheets(1).UsedRange.Rows.Count
If r = 2 Then
If Worksheets(1).Cells(r, 5).Value <> "" Then
Worksheets(1).Cells(r, 9).Value = (Worksheets(1).Cells(r, 5).Value - Worksheets(1).Cells(r, 8).Value) * 0.08
Worksheets(1).Cells(r, 6).Value = Worksheets(1).Cells(r, 8).Value + Worksheets(1).Cells(r, 9).Value
Worksheets(1).Cells(r, 10).Value = Worksheets(1).Cells(r, 5).Value - Worksheets(1).Cells(r, 6).Value
If Worksheets(1).Cells(r, 2).Value <> "" Then//实现按月统计招待费用
earn(Worksheets(1).Cells(r, 2).Value) = earn(Worksheets(1).Cells(r, 2).Value) + Worksheets(1).Cells(r, 5).Value
If Worksheets(1).<