你原来的代码有很多问题未考虑,并有错误!
修改后如下:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a&, t%
If Target.Column = 4 And Target.Count = 1 Then
Application.EnableEvents = False
W = Target.Row
Range("A" & W & ":C" & W).ClearContents
Range("E" & W & ":J" & W).ClearContents
If Target = "" Then GoTo 10
With Sheets("名单")
For a = 3 To .Range("d65536").End(3).Row
If .Range("d" & a) = Target.Text Then
t = t + 1
Range("a" & W) = .Range("c" & a)
Range("c" & W) = .Range("b" & a)
Range("e" & W) = .Range("e" & a)
Range("f" & W) = .Range("k" & a)
Range("h" & W) = .Range("g" & a)
Range("j" & W) = t
End If
Next a
End With
10 Application.EnableEvents = True
End If
End Sub
你原来的代码有很多问题未考虑,并有错误!
修改后如下:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a&, t%
If Target.Column = 4 And Target.Count = 1 Then
Application.EnableEvents = False
W = Target.Row
Range("A" & W & ":C" & W).ClearContents
Range("E" & W & ":J" & W).ClearContents
If Target = "" Then GoTo 10
With Sheets("名单")
For a = 3 To .Range("d65536").End(3).Row
If .Range("d" & a) = Target.Text Then
t = t + 1
Range("a" & W) = .Range("c" & a)
Range("c" & W) = .Range("b" & a)
Range("e" & W) = .Range("e" & a)
Range("f" & W) = .Range("k" & a)
Range("h" & W) = .Range("g" & a)
Range("j" & W) = t
End If
Next a
End With
10 Application.EnableEvents = True
End If
End Sub