|
本帖最后由 lijian8003 于 2014-3-18 00:36 编辑
问题1:下述代码是统计1-100的遗漏,如何修改代码,使之能够统计0-99的遗漏?
问题2:下述代码是在B列写入数据后自动进行统计,如果需要改用宏按钮控制统计进程,如何修改代码?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Set Target = Target(1, 1)
If Target.Column < 3 And Target.Column > 1 And Target.Row > 1 Then
Application.EnableEvents = False
b = False
For Each cel In Cells(Target.Row, "B").Resize(1, 1)
If cel = "" Then b = True: Exit For
Next
If Not b Then
For i = 1 To 100
For Each cel In Cells(Target.Row, "B").Resize(1, 1)
If cel.Value = i Then b = True: Exit For
Next
If b Then Cells(Target.Row, 3 + i).Value = 0 Else Cells(Target.Row, 3 + i).Value = 1 + IIf(Target.Row > 2, Cells(Target.Row - 1, 3 + i).Value, 0)
b = False
Next
End If
End If
Application.EnableEvents = True
End Sub
楼上正解。你的代码逻辑关系看得晕乎乎的。可以改成: - Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Set Target = Target(1, 1)
- If Len(Target) = 0 Then Exit Sub
- r = Target.Row
- If Target.Column <> 2 Or r = 1 Then Exit Sub
- Application.EnableEvents = False
- For i = 1 To 100
- Cells(r, 3 + i).Value = 1 + IIf(r > 2, Cells(r - 1, 3 + i).Value, 0)
- Next
- Cells(r, 3 + Target.Value).Value = 0
- Application.EnableEvents = True
- End Sub
复制代码另外宏按钮的可以写成 - Sub tt()
- For k = 2 To [b65536].End(3).Row
- If Len(Cells(k, 2)) > 0 Then
- For i = 1 To 100
- Cells(k, 3 + i).Value = 1 + IIf(k > 2, Cells(k - 1, 3 + i).Value, 0)
- Next
- Cells(k, 3 + Cells(k, 2)).Value = 0
- End If
- Next
- End Sub
复制代码
|
|