|
发表于 2014-8-16 08:42
|
显示全部楼层
本楼为最佳答案
问题1,建议用子程序以提高效率
问题2代码- Private Sub Worksheet_Change(ByVal Target As Range)
- On Error Resume Next
- If Not Application.Intersect([d4:e10000], Target) Is Nothing Then
- With Sheets("数据").Range(Sheets("数据").[a3], Sheets("数据").Cells(Rows.Count, 1).End(xlUp))
- Set rng1 = .Find(Cells(Target.Row, 4))
- Set rng = .Find(Cells(Target.Row, 5))
- End With
- If Not rng1 Is Nothing And Not rng Is Nothing Then
- arr = Sheets("数据").Range("a2").CurrentRegion
- ReDim brr(1 To 1, 1 To 7)
- x = rng1.Row: y = rng.Row
- For j = 2 To UBound(arr, 2)
- If arr(y, j) <> arr(x, j) Then p = p & "," & arr(1, j)
- brr(1, j - 1) = arr(y, j)
- Next
- brr(1, 7) = Mid(p, 2)
- Cells(Target.Row, 6).Resize(1, 7) = brr
- End If
- End If
- End Sub
复制代码 |
|