|
楼主 |
发表于 2017-4-2 17:40
|
显示全部楼层
Private Sub CommandButton1_Click()
Dim zhs, gs, rs, k, zd, zx, sum0, sum, h’(去掉原语句中的红色部分,原语句:Dim zhs, gs, rs, k, zd, zx, sum0, sum, h,k )
zhs = [B65536].End(xlUp).Row
gs = Cells(2, 5)
rs = Cells(2, 3)
If rs < 1 Then Exit Sub
Application.ScreenUpdating = False
If zhs > 3 Then
Range("e4:e" & zhs).ClearComments
Range("d4:d" & zhs).Interior.ColorIndex = xlNone’(红色部分应该是d,原语句:Range("e4:e" & zhs).Interior.ColorIndex = xlNone)
End If
For k = 1 To rs
sum0 = Application.WorksheetFunction.sum(Range("d" & 4 + (k - 1) * gs & ":d" & 4 + k * gs - 1))
zd = Application.Max(Range("d" & 4 + (k - 1) * gs & ":d" & 4 + k * gs - 1))
zx = Application.Min(Range("d" & 4 + (k - 1) * gs & ":d" & 4 + k * gs - 1))
sum = sum0 - zd - zx
Range("e" & 4 + (k - 1) * gs) = sum / (gs - 2)
If Range("f" & 4 + (k - 1) * gs) <> 0 Then
Range("e" & 4 + (k - 1) * gs) = Range("e" & 4 + (k - 1) * gs) + Range("f" & 4 + (k - 1) * gs)
Range("e" & (4 + (k - 1) * gs)).Select
Range("e" & (4 + (k - 1) * gs)).AddComment
Range("e" & (4 + (k - 1) * gs)).Comment.Text Text:="平均分扣除备注项"
End If
For h = 4 + (k - 1) * gs To 4 + k * gs - 1
If Cells(h, 4) = zd Then
Cells(h, 4).Interior.Color = 192
Exit For
End If
Next h
For h = 4 + (k - 1) * gs To 4 + k * gs - 1
If Cells(h, 4) = zx Then
Cells(h, 4).Interior.Color = 192
Exit For
End If
Next h
Next k
Application.ScreenUpdating = True
End Sub
|
|