jcchiang

¦^´_ 1# 52ee24
¤£¬O«ÜÀ´§Aªº°ÝÃD,¥u¤F¸Ñ¬P´Á¤é¤£±Æµ{,¨C¤éÁ`¶q¤£¶W¹L¤@©w¼Æ¶q
¸Õ¸Õ¬Ý
Sub ±Æµ{()
Application.DisplayAlerts = False
Sheets("WIP").Select
r1 = Sheets("WIP").Range("a1").CurrentRegion.Rows.Count
For i = 5 To 2197 Step 8
Sheets("±Æµ{").Range(Sheets("±Æµ{").Cells(3, i), Sheets("±Æµ{").Cells(2200, i)).Resize(, 3).ClearContents  '²M°£"§å¸¹" & "¼Æ¶q"
Next i

'³o¬q¨S¸ê®Æ¥i¨Ï¥Î,¥ýDisable,½Ð¦Û¦æEnable
'®Æ¸¹
'Sheets("WIP").Range("aj2").Formula = "=LEFT(G2,12)"
'¤u³æ¥N¸¹
'Sheets("WIP").Range("ak2").Formula = "=LEFT(A2,2)"
'¼Ð¤u
'Sheets("WIP").Range("al2").Formula = "=VLOOKUP(D:D,»sµ{¯¸¶¶§Ç»Pªí·Ç¤u®É!A:C,3,0)"
'¤é²£¯à
'Sheets("WIP").Range("am2").Formula = "=VLOOKUP(D:D,»sµ{¯¸¶¶§Ç»Pªí·Ç¤u®É!A:D,4,0)"
'»Ý­n¤Ñ¼Æ
'Sheets("WIP").Range("an2").Formula = "=K2/AL2"
'°±º¢®É¼Æ
'Sheets("WIP").Range("ao2").Formula = "=NOW()-W2"
'¹p®gµ²§ô®É¶¡
'Sheets("WIP").Range("ap2").Formula = "=VLOOKUP(A:A,¤u§@¯¸²£¥X§å¸¹©ú²Ó!B:R,17,0)"
'­pµe§¹¤u¤é
'Sheets("WIP").Range("aq2").Formula = "=IFERROR(IF(AP2+CE2<AT2,AT2,AP2+CE2),"""")"
'Â÷§¹¤u¤Ñ¼Æ
'Sheets("WIP").Range("ar2").Formula = "=IFERROR(AQ2-TODAY(),"""")"
'»sµ{³Ñ¾l¤Ñ¼Æ
'Sheets("WIP").Range("as2").Formula = "=IFERROR(VLOOKUP(D:D,»sµ{¯¸¶¶§Ç»Pªí·Ç¤u®É!A:E,5,0),"""")"
'«Øij¥æ³f¤é
'Sheets("WIP").Range("at2").Formula = "=IF(AS2="""","""",TODAY()+AS2)"
'¹w­p§¹¤u
'Sheets("WIP").Range("au2").Formula = "=IFERROR(IF(AQ2<>"""",AQ2-3,AT2),"""")"
'½Ò§O
'Sheets("WIP").Range("av2").Formula = "=VLOOKUP(D:D,RunCard!E:G,3,0)"
'»s§O
'Sheets("WIP").Range("aw2").Formula = "=VLOOKUP(D:D,RunCard!E:H,4,0)"
'»s³y§O
'Sheets("WIP").Range("ax2").Formula = "=VLOOKUP(D:D,RunCard!E:K,7,0)"
'¥ý¤@¦¸
'Sheets("WIP").Range("aj2:ax2").AutoFill (Sheets("WIP").Range("aj2:ax" & r1))
'Sheets("WIP").Range("aj2:ax" & r1) = Sheets("WIP").Range("aj2:ax" & r1).Value

'²Ä¤G¬q°Õ
'»s¦¨»Ý­n¤Ñ¼Æ
'Sheets("WIP").Range("ay2").Formula = "=SUMIFS(RunCard!$I:$I,RunCard!$A:$A,WIP!$G2,RunCard!$K:$K,WIP!AY$1)"
'Sheets("WIP").Range("ay2").AutoFill (Sheets("WIP").Range("ay2:ay" & r1))
'Sheets("WIP").Range("ay2:ay" & r1).AutoFill (Sheets("WIP").Range("ay2:cd" & r1))
'Sheets("WIP").Range("ay2:cd" & r1) = Sheets("WIP").Range("ay2:cd" & r1).Value
'Á`¤Ñ¼Æ
'Sheets("WIP").Range("ce2").Formula = "=SUM(AY2:cd2)"
'Sheets("WIP").Range("ce2").AutoFill (Sheets("WIP").Range("ce2:ce" & r1))

'²Ä¤T¬q°Õ
'­pºâ¤é´Á
'Sheets("WIP").Range("cf2").Formula = "=IF($AU2=0,"""",$AU2-SUM(AY2:$cd2))"
'Sheets("WIP").Range("cf2").AutoFill (Sheets("WIP").Range("cf2:cf" & r1))
'Sheets("WIP").Range("cf2:cf" & r1).AutoFill (Sheets("WIP").Range("cf2:dk" & r1))
'Sheets("WIP").Range("cf2:dk" & r1) = Sheets("WIP").Range("cf2:dk" & r1).Value

'²Ä¥|¬q°Õ
'­pºâ¤u§@¤é
'Sheets("WIP").Range("cn2").Formula = "=IFERROR(ABS($AU2-BT2-NETWORKDAYS.INTL(BT2,$AU2,11)),"""")"
'Sheets("WIP").Range("cn2").AutoFill (Sheets("WIP").Range("cn2:cn" & r1))
'Sheets("WIP").Range("cn2:cn" & r1).AutoFill (Sheets("WIP").Range("cn2:dg" & r1))
'Sheets("WIP").Range("cn2:dg" & r1) = Sheets("WIP").Range("cn2:dg" & r1).Value

'²Ä¤­¬q°Õ
'¤é´Á+¤u§@¤é
'Sheets("WIP").Range("dl2").Formula = "=IF($AU2="""","""",IF((TEXT(cf2,""ddd"")=""Sun""),VALUE(TEXT(cf2+1,""yyyy/mm/dd"")),VALUE(TEXT(cf2,""yyyy/mm/dd""))))"
'Sheets("WIP").Range("dl2").AutoFill (Sheets("WIP").Range("dl2:dl" & r1))
'Sheets("WIP").Range("dl2:dl" & r1).AutoFill (Sheets("WIP").Range("dl2:eq" & r1))
'Sheets("WIP").Range("dl2:eq" & r1) = Sheets("WIP").Range("dl2:eq" & r1).Value


'¹w­p§¹¤u(Âà´«)
'Sheets("WIP").Range("er2").Formula = "=IFERROR(VALUE(TEXT(AU2,""yyyy/mm/dd"")),"""")"
'Sheets("WIP").Range("er2").AutoFill (Sheets("WIP").Range("er2:er" & r1))
'Sheets("WIP").Range("er2:er" & r1) = Sheets("WIP").Range("er2:er" & r1).Value

'±Æµ{
Sheets("±Æµ{").Select

'01¹p®g²M¬~«e
For j = 2 To r1
   dl = Sheets("WIP").Range("dl" & j)
   If dl <> "" Then
      lot = Sheets("WIP").Range("a" & j)
      Wip = Sheets("WIP").Range("k" & j) '¼W¥[¼Æ¶q¥H«K²Î­p
'§ä»sµ{§O
      Dim c As Range
      Set c = Sheets("±Æµ{").Range("b:b")
      Fnd = c.Find(what:="01¹p®g²M¬~«e", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
        
'§ä¤é´ÁÄæ
      Dim c1 As Range
      Set c1 = Sheets("±Æµ{").Range("a1:cfm1")
      fnd1 = c1.Find(what:=dl, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Column
'§ä¤é´Á¦C
      Dim c2 As Range
      Set c2 = Sheets("±Æµ{").Columns(fnd1)
      fnd2 = c2.Find(what:="", after:=Cells(1, fnd1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
      plus = Cells(Fnd, fnd1 - 2)
      If plus < 2000 Then    'Á`¶q<2000±Æ¤J¦æµ{
         Cells(fnd2, fnd1) = lot
         Cells(fnd2, fnd1).Offset(, 2) = Wip 'lotªº¼Æ¶q
      Else
         For x = 6 To (Cells(2, fnd1).End(2).Column - fnd1) Step 8    '§PÂ_¨ä¥L¤é´ÁªºÁ`¶q
            If Cells(Fnd, fnd1).Offset(, x) < 2000 And Cells(Fnd, fnd1).Offset(-2, (x + 3)) <> "¤é" Then  '±Æ°£"¤é",§PÂ_¨ä¥L¤u§@¤éªºÁ`¶q<2000
               Dim c3 As Range
               Set c3 = Sheets("±Æµ{").Columns(fnd1 + (x + 2))
               fnd3 = c3.Find(what:="", after:=Cells(1, fnd1 + (x + 2)), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
               Cells(fnd3, fnd1).Offset(, (x + 2)) = lot
               Cells(fnd3, fnd1).Offset(, (x + 4)) = Wip
               Exit For
            End If
         Next
      End If
   End If
Next j
End Sub

jcchiang

¦^´_ 4# 52ee24

1.¬P´Á¤é¤£¯à±Æ¡A­n¶¶©µ
If Cells(Fnd, fnd1).Offset(, x) < 2000 And Cells(Fnd, fnd1).Offset(-2, (x + 3)) <> "¤é" Then  '±Æ°£"¤é",§PÂ_¨ä¥L¤u§@¤éªºÁ`¶q<2000
a.Á`¶qªº­È½Ð¦Û¦æ½Õ¾ã
b.³æ¤éÁ`¶q¤j©ó­­¨î,©¹«á§äÁ`¶q¥¼¶W¹L­­¨îªº¤é´Á±Æ¤J,¹J¬P´Á¤éª½±µ¶¶©µ
2.¨C¤ÑªºÁ`±Æµ{¶q¤p©ó2200
If plus < 2000 Then    'Á`¶q<2000±Æ¤J¦æµ{
a.Á`¶qªº­È½Ð¦Û¦æ½Õ¾ã