|
![](http://www.excelpx.com/data/attachment/forum/202006/08/194126lvdoqvdhvz5hdvqv.jpg) - Private Sub Worksheet_Change(ByVal Target As Range)
- Dim arr, arr1
- Dim iR&, x&, i&, n&, j&, endrow&
- Dim T As String
- If Target.Address = "$B$1" Then
- T = Range("b1").Value
- If Len(T) = 4 Then j = 1 Else j = 2
- With Sheets("sheet1")
- iR = .Range("A65536").End(xlUp).Row
- arr = .Range("A2:l" & iR).Value
- ReDim arr1(1 To UBound(arr), 1 To UBound(arr, 2))
- For x = 1 To UBound(arr)
- If arr(x, j) = T Then
- i = i + 1
- For n = 1 To 12
- arr1(i, n) = arr(x, n)
- Next n
- End If
- Next x
- End With
- Range("A3").Resize(UBound(arr1), UBound(arr1, 2)) = arr1
- End If
- If Target.Row > 2 And Target.Column = 5 And Target.Count = 1 Then
- Cells(Target.Row, 6) = Cells(Target.Row, 6) + Target.Value
- Cells(Target.Row, 8) = Cells(Target.Row, 8) + Target.Value
- If Target.Value > 0 Then
- With Sheets("sheet1")
- For x = 1 To .Range("A65536").End(xlUp).Row
- If .Cells(x, 1) = Cells(Target.Row, 1) Then
- .Cells(x, 5) = Cells(Target.Row, 5).Value
- .Cells(x, 6) = Cells(Target.Row, 6).Value
- .Cells(x, 8) = Cells(Target.Row, 8).Value
- .Cells(x, 12) = Cells(Target.Row, 12).Value
- End If
- Next x
- End With
- Else
- With Sheets("Sheet3")
- endrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
- .Range("A" & endrow & ":L" & endrow).Value = Range("A" & Target.Row & ":L" & Target.Row).Value
- End With
- End If
- End If
- If Target.Row > 2 And Target.Column = 12 And Target.Count = 1 Then
- With Sheets("sheet1")
- For x = 1 To .Range("A65536").End(xlUp).Row
- If .Cells(x, 1) = Cells(Target.Row, 1) Then
- .Cells(x, 12) = Cells(Target.Row, 12).Value
- End If
- Next x
- End With
- End If
- End Sub
复制代码 不知道是不是楼主想要的![](static/image/smiley/default/smile.gif) |
|