ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¨C¤éÁ`¶q­­¨î

¨C¤éÁ`¶q­­¨î

¦U¦ì«e½ú¦n
§Úªº»Ý¨D¬O­n§âWIP(Sheet)¦U»sµ{ªº±Æµ{¤é´Á
¾ã²z¨ì±Æµ{(sheet)¸Ì
¦U»sµ{ªº¨C¤é¥Í²£Á`¶q¤£©T©w
¸ê®Æ«D±`¦h
§Úªº·Qªk¬O¤ñ¹ï±Æµ{¸Ìªº¨C¤éÁ`¼Æ¶q
¶W¹L¨C¤é¥Í²£Á`¶qªº¸Ü´N¶¶±Æ¦Ü¤U¤@¤Ñ
ÁÙ­nÁ׶}¬P´Á¤Ñ
¦]¬°§Ú¥u·|¥Î°j°é¼g
¤@ª½¶¶±À
³£·|²Ö¿n¨ì¬Y¤@¤Ñ(¨Ò¦p¹Ï¤@)
¨Ò:
±Æµ{(sheet)ªº01¹p®g²M¬~«e_¯¸
¨C¤é¥Í²£Á`¶q¬O2200
¥ý¨Ì¾ÚWIP(sheet)DLÄæ¦ì¤é´Á
§âAÄæ_§å¸¹¸ê®Æ±a¤J¹ïÀ³ªº±Æµ{¤é´Á¸Ì
±a¤J®É¤ñ¹ï¤w¸g±Æ¤JªºÁ`¼Æ
¶W¹Lªº´N³Q¤J¹j¤Ñ
µ{¦¡¦p¤U
    '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)
        
            '§ä»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
               Cells(fnd2, fnd1) = lot
               
                    plus1 = Cells(fnd, fnd1 + 7)
                    ElseIf plus1 < 2000 Then
                    Dim c3 As Range
                    Set c3 = Sheets("±Æµ{").Columns(fnd1 + 8)
                    fnd3 = c3.Find(what:="", after:=Cells(1, fnd1 + 8), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                    Cells(fnd3, fnd1 + 8) = lot
                    
                        plus2 = Cells(fnd, fnd1 + 15)
                        ElseIf plus2 < 2000 Then
                        Dim c4 As Range
                        Set c4 = Sheets("±Æµ{").Columns(fnd1 + 16)
                        fnd4 = c4.Find(what:="", after:=Cells(1, fnd1 + 16), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                        Cells(fnd4, fnd1 + 16) = lot
                            plus3 = Cells(fnd, fnd1 + 23)
                            ElseIf plus3 < 2000 Then
                            Dim c5 As Range
                            Set c5 = Sheets("±Æµ{").Columns(fnd1 + 24)
                            fnd5 = c5.Find(what:="", after:=Cells(1, fnd1 + 24), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                            Cells(fnd5, fnd1 + 24) = lot
                            plus4 = Cells(fnd, fnd1 + 31)
                            ElseIf plus4 < 2000 Then
                            Dim c6 As Range
                            Set c6 = Sheets("±Æµ{").Columns(fnd1 + 32)
                            fnd6 = c6.Find(what:="", after:=Cells(1, fnd1 + 32), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                            Cells(fnd6, fnd1 + 32) = lot
                            plus5 = Cells(fnd, fnd1 + 39)
                            ElseIf plus5 < 2000 Then
                            Dim c7 As Range
                            Set c7 = Sheets("±Æµ{").Columns(fnd1 + 40)
                            fnd7 = c7.Find(what:="", after:=Cells(1, fnd1 + 40), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                            Cells(fnd7, fnd1 + 40) = lot
                            plus6 = Cells(fnd, fnd1 + 47)
                            ElseIf plus6 < 2000 Then
                            Dim c8 As Range
                            Set c8 = Sheets("±Æµ{").Columns(fnd1 + 48)
                            fnd8 = c8.Find(what:="", after:=Cells(1, fnd1 + 48), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
                            Cells(fnd8, fnd1 + 48) = lot
                        Else
                       
                    
                  End If
     
        End If
        Next j
   

½Ð¦U¦ìÀ°À°¦£¤F!!!!
·d¤£¥X¨Ó
·|³Q¦ÑÁó§ì¥h®I
¥ýÁÂÁ«e½ú­Ìªº¤j®¦¤j¼w
¹Ï¤@.png

±Æµ{­pµe-0511.rar (958.42 KB)

±Æµ{­pµe-0511.rar (956.21 KB)

±Æµ{­pµe-0511.rar (956.21 KB)

¦^´_ 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

TOP

¦^´_ 2# jcchiang


¤£¦n·N«ä
ÀɮפӤj¤F
¥þ³¡sheet©ñ¤£¤W¥h
§Ú¥u¯à¥ý°w¹ï
¾ã²z±Æµ{±a¤J±Æµ{Sheet¸Ì

TOP

¦^´_ 2# jcchiang


    ±Æµ{ªº­ì«h¬O
1.¬P´Á¤é¤£¯à±Æ¡A­n¶¶©µ
2.¨C¤ÑªºÁ`±Æµ{¶q¤p©ó2200

TOP

¦^´_ 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ªº­È½Ð¦Û¦æ½Õ¾ã

TOP

¦^´_ 5# jcchiang


   ÁÂÁÂ jcchiang
¥i¥H¶]¤F!!
¥t¥~½Ð°Ý§Ú­n½Æ»sµ{¦¡
¦V¤U´M§ä§Oªº»sµ{
¦ý¤@ª½¶K¿ù
§ä¤£¨ì¤U­Ó»sµ{ªº¦C¼Æ
§Ú§âÅܼƳ£¼W¥["02"
³o¼Ë­þ¸Ì¿ù¤F
³Â·ÐÀ°¦£
ÁÂÁ§A
   '02¹p®g¿z¤Õ«á
For i = 2 To r1
   dm = Sheets("WIP").Range("dm" & i)
   If dm <> "" Then
      lot02 = Sheets("WIP").Range("a" & i)
      Wip02 = Sheets("WIP").Range("k" & i)       Dim c02 As Range
      Set c02 = Sheets("??{").Range("b:b")
      Fnd02 = c02.Find(what:="02¹p®g¿z¤Õ«á", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
        

      Dim c102 As Range
      Set c102 = Sheets("??{").Range("a1:cfm1")
      fnd102 = c102.Find(what:=dm, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Column

      Dim c202 As Range
      Set c202 = Sheets("±Æµ{").Columns(fnd102)
      fnd202 = c202.Find(what:="", after:=Cells(1, fnd102), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
      plus02 = Cells(Fnd02, fnd102 - 2)
      If plus02 < 2000 Then   
         Cells(fnd202, fnd102) = lot02
         Cells(fnd202, fnd102).Offset(, 2) = Wip02
      Else
         For x = 6 To (Cells(2, fnd102).End(2).Column - fnd102) Step 8   
            If Cells(Fnd02, fnd102).Offset(, x) < 2000 And Cells(Fnd02, fnd102).Offset(-2, (x + 3)) <> "¤é" Then
               Dim c302 As Range
               Set c302 = Sheets("±Æµ{").Columns(fnd102 + (x + 2))
               fnd302 = c302.Find(what:="", after:=Cells(1, fnd102 + (x + 2)), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, matchbyte:=False, searchformat:=False).Row
               Cells(fnd302, fnd102).Offset(, (x + 2)) = lot02
               Cells(fnd302, fnd102).Offset(, (x + 4)) = Wip02
               Exit For
            End If
         Next
      End If
   End If
Next i

TOP

¦^´_ 2# jcchiang


   ÁÂÁÂ jcchiang
°ÝÃD¤w¸g¸Ñ¨M¤F
ÁÂÁ§AªºÀ°¦£!!!!

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD