|
本帖最后由 microyip 于 2020-1-16 11:56 编辑
在Thisworkbook里建立
- Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
- Dim vData As Variant, nRow As Long, nCol As Long, vFill As Variant, nVal As Double, nLastTime As Long
- With Sh
- nRow = Target.Row
- nCol = Target.Column
- If .Name Like "积分表*年" And nRow > 4 And nCol > 16 Then
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- vData = .Cells(nRow, 17).Resize(, .UsedRange.Columns.Count - .UsedRange.Column + 1 - 14).Value
- ReDim vFill(1 To 5)
- For nCol = UBound(vData, 2) To 1 Step -1
- nVal = Val(vData(1, nCol))
- If nVal > 0 Then
- vFill(1) = vFill(1) + 1
- vFill(2) = vFill(2) + nVal
- If nLastTime < 10 Then
- vFill(4) = vFill(4) + nVal
- nLastTime = nLastTime + 1
- End If
- End If
- Next
- If nLastTime > 0 Then vFill(3) = vFill(4) / nLastTime
- .Cells(nRow, "J").Resize(, 4) = vFill
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End If
- End With
- End Sub
复制代码 |
|