|
本帖最后由 hwc2ycy 于 2013-2-26 19:59 编辑
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim rg As Range
- Dim arr(1 To 9)
- Dim arrPos, i As Byte
- arrPos = Array(5, 6, 4, 7, 1, 8, 9, 10, 11)
- If Target.Count > 1 Then Exit Sub
- If Len(Target.Value) = 0 Then Exit Sub
- Select Case Target.Column
-
- Case 4
- '处理第4列
- With Sheet2
- Set rg = .Columns("d").Find(what:=Target.Value, lookat:=xlWhole)
- If rg Is Nothing Then
- '清空当前行内容
- Cells(Target.Row, "b").Resize(, 9) = arr
- Exit Sub
- End If
- irow = rg.Row
- For i = LBound(arrPos) To UBound(arrPos)
- arr(i + 1) = .Cells(irow, arrPos(i))
- Next
- End With
- '保留当前行的5,6列内容
- arr(5) = Cells(Target.Row, "f")
- arr(6) = Cells(Target.Row, "g")
- Cells(Target.Row, "b").Resize(, 9) = arr
- Case 6
- '处理第6列
- With Sheet2
- Set rg = .Columns("d").Find(what:=Target.Offset(, -2).Value, lookat:=xlWhole)
- If rg Is Nothing Then
- Cells(Target.Row, "b").Resize(, 9) = arr
- Exit Sub
- End If
- irow = rg.Row
- For i = LBound(arrPos) To UBound(arrPos)
- arr(i + 1) = .Cells(irow, arrPos(i))
- Next
- End With
- arr(5) = Cells(Target.Row, "f")
- arr(6) = Cells(Target.Row, "g")
- Cells(Target.Row, "b").Resize(, 9) = arr
- Case Else
- Exit Sub
- End Select
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim rg As Range
- Dim arr(1 To 9)
- Dim arrPos, i As Byte
- arrPos = Array(5, 6, 4, 7, 1, 8, 9, 10, 11)
- If Target.Count > 1 Then Exit Sub
- If Len(Target.Value) = 0 Then Exit Sub
- Select Case Target.Column
-
- Case 4
- '处理第4列
- With Sheet2
- Set rg = .Columns("d").Find(what:=Target.Value, lookat:=xlWhole)
- If rg Is Nothing Then
- '清空当前行内容
- Cells(Target.Row, "b").Resize(, 9) = arr
- Exit Sub
- End If
- 'Application.EnableEvents = False
- irow = rg.Row
- For i = LBound(arrPos) To UBound(arrPos)
- arr(i + 1) = .Cells(irow, arrPos(i))
- Next
- End With
- '保留当前行的5,6列内容
- arr(5) = Cells(Target.Row, "f")
- arr(6) = Cells(Target.Row, "g")
- Cells(Target.Row, "b").Resize(, 9) = arr
- Case 6
- '处理第6列
- With Sheet2
- Set rg = .Columns("d").Find(what:=Target.Offset(, -2).Value, lookat:=xlWhole)
- If rg Is Nothing Then
- Cells(Target.Row, "b").Resize(, 9) = arr
- Exit Sub
- End If
- irow = rg.Row
- For i = LBound(arrPos) To UBound(arrPos)
- arr(i + 1) = .Cells(irow, arrPos(i))
- Next
- End With
- arr(5) = Cells(Target.Row, "f")
- arr(6) = Cells(Target.Row, "g")
- Cells(Target.Row, "b").Resize(, 9) = arr
- Case Else
- Exit Sub
- End Select
- End Sub
复制代码 其实监视 Change事件是否更好了。
|
|