|
发表于 2017-7-26 08:50
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- If Target.Address <> [g2].Address Then Exit Sub
- Union([a20:j49], [e4:j16]).ClearContents
- If Target = "" Then Exit Sub
- Dim rng As Range
- Set rng = Sheets(3).Columns(1).Find(Target.Offset(, -3), lookat:=xlWhole)
- If rng Is Nothing Then Exit Sub
- Dim arr, brr(1 To 30, 1 To 10), crr(1 To 13, 1 To 6), i&, r&, r1&, s$, s1$, d4$
- s = [d2]: s1 = Target: d4 = Range("d4").Value
- arr = Sheets(2).Range("b3:m" & Sheets(2).[b65536].End(3).Row)
- For i = 1 To UBound(arr)
- If arr(i, 1) = s And arr(i, 2) = s1 Then
- If arr(i, 4) <> d4 Then
- r = r + 1
- brr(r, 1) = r
- brr(r, 2) = arr(i, 4)
- brr(r, 3) = arr(i, 3)
- brr(r, 4) = arr(i, 6)
- brr(r, 5) = arr(i, 11)
- brr(r, 6) = arr(i, 7)
- brr(r, 7) = arr(i, 8)
- brr(r, 8) = arr(i, 9)
- brr(r, 9) = arr(i, 10)
- brr(r, 10) = arr(i, 12)
- Else
- r1 = r1 + 1
- crr(r1, 1) = arr(i, 5)
- crr(r1, 2) = arr(i, 9)
- crr(r1, 3) = arr(i, 8)
- crr(r1, 4) = arr(i, 9)
- crr(r1, 5) = arr(i, 10)
- crr(r1, 6) = arr(i, 12)
- End If
- End If
- Next i
- [a20].Resize(30, 10) = brr
- [e4].Resize(13, 6) = crr
- End Sub
复制代码 |
|