|
楼主 |
发表于 2017-4-14 09:18
|
显示全部楼层
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim r%, i%, m%
- Dim arr, brr(1 To 18, 1 To 9)
- Dim ws As Worksheet
- If Target.Count > 1 Then Exit Sub
- If Target.Address = "$C$4" Or Target.Address = "$C$3" Then
- Application.EnableEvents = False
- With Worksheets("指令")
- PH = CStr([c4].Value)
- NM = CStr([c3].Value)
- arr = Sheets("产品信息").UsedRange
- For i = 4 To UBound(arr)
- If arr(i, 3) = NM Then
- For J = 4 To 18
- .Cells(J, "Z") = arr(i, J + 5)
- Next
- End If
- Next
- r = .Cells(.Rows.Count, 26).End(xlUp).Row
- crr = Application.Transpose(.Range("z4:z" & r))
- For Each x In crr
- With Worksheets(x)
- r = .UsedRange.Find(what:="*", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
- For i = 5 To r
- If .Cells(i, 2) = PH And .Cells(i, 1) = NM Then
- r0 = .Cells(i, 5).MergeArea.Row
- m = m + 1
- brr(m, 1) = m
- brr(m, 2) = .Cells(r0, 5)
- brr(m, 3) = .Cells(r0, 6)
- brr(m, 4) = .Cells(2, 19)
- brr(m, 5) = .Cells(r0, 7)
- brr(m, 6) = .Cells(2, 20)
- brr(m, 7) = .Cells(i, 4)
- brr(m, 8) = .Cells(r0, 10)
- brr(m, 9) = .Cells(r0, 11) & .Cells(r0, 12)
- End If
- Next
- End With
- Next
- .Range("b9").Resize(UBound(brr), UBound(brr, 2)) = brr
- arr = Sheets("生产计划").Range("A3:K" & Sheets("生产计划").Range("A65536").End(3).Row)
- For i = 1 To UBound(arr)
- If arr(i, 2) = NM And arr(i, 5) = PH Then
- .Cells(2, "J") = arr(i, 7)
- .Cells(3, "G") = arr(i, 1)
- .Cells(4, "G") = arr(i, 4)
- .Cells(4, "J") = arr(i, 3)
- .Cells(5, "C") = arr(i, 11)
- .Cells(6, "J") = arr(i, 6)
- Range("J6").NumberFormatLocal = "yyyy""年""m""月""d""日"""
- End If
- Next
- End With
- Application.EnableEvents = True
- End If
- End Sub
- Sub tt()
- [c4].Formula = Replace("=INDIRECT(@苹果!B@&INDIRECT(@$M$2@)+4)", "@", Chr(34))
- End Sub
复制代码 |
|