|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Not Application.Intersect(Target, Range("c1,c12,c23")) Is Nothing Then
- Dim Arr, i&, ArrR(1 To 10000, 1 To 9)
- Dim j As Byte, M&, N&, S As Byte
- With Sheet1
- Arr = .Range("A2:n" & .Cells(.Rows.Count, 1).End(3).Row).Value
- End With
- For i = 3 To UBound(Arr)
- If Arr(i, 1) = Target.Value Then
- S = 0
- If Arr(i, 3) = "ÄÐ" Then
- M = M + 1
- ArrR(M, 1) = Arr(i, 2)
- For j = 4 To UBound(Arr, 2)
- If Len(Arr(i, j)) Then
- S = S + 1
- ArrR(M, S + 1) = Arr(1, j)
- End If
- Next j
- Else
- N = N + 1
- ArrR(N, 6) = Arr(i, 2)
- For j = 4 To UBound(Arr, 2)
- If Len(Arr(i, j)) Then
- S = S + 1
- ArrR(N, S + 6) = Arr(1, j)
- End If
- Next j
- End If
- End If
- Next i
- With Cells(Target.Row + 3, 1)
- .Resize(8, 9).Clear
- M = Application.Min(8, Application.Max(M, N))
- If M > 0 Then .Resize(M, 9) = ArrR
- End With
- End If
- End Sub
复制代码 这样试试 |
|