|
Private Sub Worksheet_Change(ByVal Target As Range)
For r = 3 To 112
If Target.Column = 8 And Cells(r, 8) = "" Then
Exit Sub
Else
End If
Next
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Integer
For r = 3 To 112
If Target.Column = 8 And Cells(r, 8) <> 0 Then
Cells(r, 30).Value = Val(Cells(r, 22)) - Val(Cells(r, 24)) _
- Val(Cells(r, 25)) - Val(Cells(r, 26)) _
- Val(Cells(r, 27)) - Val(Cells(r, 28)) _
- Val(Cells(r, 29))
Cells(r, 22).Value = Cells(r, 14) + Cells(r, 15) _
+ Cells(r, 17) - Val(Cells(r, 16)) _
+ Val(Cells(r, 19)) + Val(Cells(r, 20)) _
+ Val(Cells(r, 21)) + Val(Cells(r, 18))
Cells(r, 23).Value = Val(Cells(r, 22)) _
- Val(Cells(r, 17)) _
- Val(Cells(r, 26)) - 2000
End If
Next
End Sub
我的目的是想当表中第八列数据变化的时候,激活change事件与selectchange事件,后面同行的单元格自动计算,代码如上,但是当我运行的时候却起不了效果,不知道原因在哪里,请高手指点
本帖最后由 zjdh 于 2011-8-12 20:09 编辑
你第8列的数据与参与计算的各列没有任何关系(无公式),最后计算当然不变化啦!
另外:计算先后你也搞错啦,我原来没在意,应该如下: - Private Sub Worksheet_Change(ByVal Target As Range)
- If Not Intersect(Range("H3:H114"), Target) Is Nothing Then
- r = Target.Row
- If Target.Count = 1 And Cells(r, 8) <> 0 Then
- Cells(r, 22).Value = Cells(r, 14) + Cells(r, 15) _
- + Cells(r, 17) - Val(Cells(r, 16)) _
- + Val(Cells(r, 19)) + Val(Cells(r, 20)) _
- + Val(Cells(r, 21)) + Val(Cells(r, 18))
- Cells(r, 23).Value = Val(Cells(r, 22)) _
- - Val(Cells(r, 17)) _
- - Val(Cells(r, 26)) - 2000
- Cells(r, 30).Value = Val(Cells(r, 22)) - Val(Cells(r, 24)) _
- - Cells(r, 25) - Cells(r, 26) _
- - Val(Cells(r, 27)) - Val(Cells(r, 28)) _
- - Val(Cells(r, 29))
- End If
- End If
- End Sub
复制代码
|
|