仅供参考
将公式用放在VBA
运用工作表Change事件
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Row < 3 Then Exit Sub
- If Target.Count > 1 Then Exit Sub
- If Target.Column < 6 Or Target.Column > 34 Then Exit Sub
- Dim r As Long, c As Integer
- r = Target.Row '目标列
- c = Target.Column '一标栏
- If c = 16 Or c = 17 Then
- Cells(r, 18) = Cells(r, 17) - Cells(r, 16) '代理差价
- ElseIf c = 15 Or c = 22 Then
- Cells(r, 25) = Cells(r, 22) - Cells(r, 15) '末开票数量
- ElseIf (c >= 5 And c <= 7) Or c = 17 Or c = 33 Or c = 34 Then
- With Application.WorksheetFunction
- Cells(r, 35) = .SumIf(Range(Cells(3, 5), Cells(r, 5)), Cells(r, 5), Range(Cells(3, 6), Cells(r, 6))) + .SumIf(Range(Cells(3, 5), Cells(r, 5)), Cells(r, 5), Range(Cells(3, 17), Cells(r, 17))) - .SumIf(Range(Cells(3, 5), Cells(r, 5)), Cells(r, 5), Range(Cells(3, 33), Cells(r, 33))) '欠款总额(按客户)
- Cells(r, 36) = .SumIf(Range(Cells(3, 5), Cells(r, 5)), Cells(r, 5), Range(Cells(3, 7), Cells(r, 7))) + .SumIf(Range(Cells(3, 5), Cells(r, 5)), Cells(r, 5), Range(Cells(3, 34), Cells(r, 34))) '逾期欠款(按客户)
- Cells(r, 37) = .SumIf(Range(Cells(3, 3), Cells(r, 3)), Cells(r, 3), Range(Cells(3, 6), Cells(r, 6))) + .SumIf(Range(Cells(3, 3), Cells(r, 3)), Cells(r, 3), Range(Cells(3, 17), Cells(r, 17))) - .SumIf(Range(Cells(3, 3), Cells(r, 3)), Cells(r, 3), Range(Cells(3, 33), Cells(r, 33))) '欠款总额(按代理商)
- Cells(r, 38) = .SumIf(Range(Cells(3, 3), Cells(r, 3)), Cells(r, 3), Range(Cells(3, 7), Cells(r, 7))) + .SumIf(Range(Cells(3, 3), Cells(r, 3)), Cells(r, 3), Range(Cells(3, 34), Cells(r, 34))) '逾期欠款(按代理商)
- End With
- End If
- End Sub
复制代码 |