|
- Option Explicit
- Sub tongji()
- '既然是常数,就在这里直接定义金额,不要再从表格中读取
- Const QY = 100
- Const V = 150
-
- Dim shtName As Worksheet
- Dim recNum As Integer
- Dim t As Integer
- Dim q As Currency, R As Currency, b1 As Currency, j As Currency, b2 As Currency, k As Currency, S As Currency, CW As Currency
-
- Set shtName = Sheets("汇总该年")
-
- recNum = shtName.Cells(Rows.Count, 1).End(xlUp).Row - 1
-
- '清空计算区域
- shtName.Cells(2, "E").Resize(recNum, 7).Clear
-
- '上日结余赋初值
- b1 = 0
- '设置从 2 开始,是为了避开标题行
- For t = 2 To recNum + 1
- '当日初始化,将上日结余、每日支出、存款上限写入表格
- Cells(t, "H") = Format(b1, "#,##0.00")
- Cells(t, "F") = Format(QY, "#,##0.00")
- Cells(t, "J") = Format(V, "#,##0.00")
- '从表格当日收入比例
- q = shtName.Cells(t, "D")
- '计算当日实际收入
- If q - 2 > 0 Then
- R = 2000 * (q - 2) * 0.9 * 0.001
- Else
- R = 0
- End If
- '上日结余 + 今日收入 > 存款上限
- ' 超过存款上限部分,作为今日可花销金额
- ' 其余部分,作为可存入
- '上日结余 + 今日收入 < 存款上限
- ' 全部作为可存入
- j = R + b1
- If j > V Then
- S = j - V
- b2 = V
- Else
- S = 0
- b2 = j
- End If
- '可存入 - 每日支出
- '透支:借款,并上日结余 = 0
- '盈余:不借款,上日结余 = 盈余
- k = b2 - QY
- If k < 0 Then
- CW = -k
- b1 = 0
- Else
- CW = 0
- b1 = k
- End If
- Cells(t, "E") = Format(R, "#,##0.00")
- Cells(t, "G") = Format(S, "#,##0.00")
- Cells(t, "I") = Format(CW, "#,##0.00")
- Cells(t, "K") = Format(b2, "#,##0.00")
- Next t
- End Sub
复制代码 |
|