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

[µo°Ý] VBA ½Æ»sdata°ÝÃD(¸óSheet)

¦^´_ 10# ­ã´£³¡ªL

ª©¥D§A¦n,
¢°¡D¨C­ÓIPQC¬O§_¦U¹ïÀ³¤@­ÓFQC¡H¡@¥BÀɮצWºÙ«eºó¬Û¦P¡H
¡@¡@¨Ò¦p¡GL-26P-2018-IPQC¡@¹ïÀ³¡@L-26P-2018-FQC¡A¬Û¦P¬°"L-26P-2018"
->   ¨ä¹êÀɮ׬O¤@¹ï¤@ªº, ¤£·|¦@¥Î, ©Ò¥Hµ{¦¡¥i¥H¤À§O³]©w¤£·|¼vÅT

¢±¡DIPQCªº¢ÏÄ涵¥Ø¼Æ¶q¬O¡e©T©w¡fªº¡H¡@¥B¥²»PFQC¬Û¹ïÀ³¡H
->    IPQC A Äæ¦ì¦¡©T©wªº, ¥B»PFQC ¬Û¹ïÀ³

¢²¡D§ì¢´®æ©Î§ì¢±®æªº³W«h¬O¤°»ò¡H
¡@¡@©ÎªÌ¡A¥i§Q¥ÎIPQCªº¢ÙÄæ¡A§ì¢´µ§ªº¿é¤J¢´¡A§ì¢±µ§ªº¿é¤J¢±¡A´N¥Î³o¨Ó§PÂ_§ì´X®æ
->   ¨ä¹ê§Úªº·Qªk¬O, °w¹ï¤£¦P»Ý¨D³]©w¤£¦PÀÉ®×, ª½±µ±NÀɮ׸j¦º, ©È¨Ï¥ÎªÌ·|¦³key in ¿ù»~­·ÀI

TOP

¦^´_ 9# dea172


2.IPQC ÀÉ®× HÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¥u§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-15-3)
¹Ï¤ù¬Ý¬OMÄæ¦ì???
¬O§_¥Î"M"¨Ó§PÂ_¨ú2®æ???  ÁÙ¬O[³Ì«á¤@µ§]¨ú2®æ???

2.IPQC ÀÉ®× P/MÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¦U§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-26P)
¬O§_¥Î"P"©Î"M"§PÂ_¨ú2®æ???  ©Î¬O[³Ì«á2µ§]¨ú2®æ???

µM«á, FQCÀɮצWºÙ¤£¬O©T©wªº, µLªk¼g¦º¦bµ{¦¡¸Ì(¤£µM¨C¦¸³£­n¤â§ï)???

TOP

¦^´_ 12# ­ã´£³¡ªL

ª©¥D§A¦n,
¤£¦n·N«ä, ­n¦A¦¸³Â·Ð
2.IPQC ÀÉ®× HÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¥u§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-15-3)
¹Ï¤ù¬Ý¬OMÄæ¦ì???
¬O§_¥Î"M"¨Ó§PÂ_¨ú2®æ???  ÁÙ¬O[³Ì«á¤@µ§]¨ú2®æ???
-> ³o个·|ÀHµÛ¸ê®Æ¦Ó¤£¦P, ³o¬O§_¥i¥H¨Ì»Ý¨D¦Ó¦Û¦æÅܧó?
EX: ­n"M"Äæ¦ì, ´N±qµ{¦¡¤¤§ó§ï¬°"M"Äæ¦ì
       ­n"H"Äæ¦ì, ´N±qµ{¦¡¤¤§ó§ï¬°"H"Äæ¦ì

2.IPQC ÀÉ®× P/MÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¦U§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-26P)
¬O§_¥Î"P"©Î"M"§PÂ_¨ú2®æ???  ©Î¬O[³Ì«á2µ§]¨ú2®æ???
->  ¹ï, ³o¤ñ¸û¯S§O, ³o个Àɮ׬°[³Ì«á2µ§]¨ú2®æ
µM«á, FQCÀɮצWºÙ¤£¬O©T©wªº, µLªk¼g¦º¦bµ{¦¡¸Ì(¤£µM¨C¦¸³£­n¤â§ï)???
->  FQCÀɮצWºÙ³o个§Ú¥i¥H¨Ì·Ó§ÚªºÀɮ׻ݨD¤â°Ê§ó§ïÅܧó

TOP

¦^´_ 13# dea172
  1. Sub FQC_IPQC()
  2. Dim Ay(0 To 1), A As Range, C As Range, y&, d#, m&, i!, Lot1$, Lot2, f, fd$, fs$, fc$
  3. Do
  4. fd = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "½Ð¿ï¾ÜIPQCÀÉ", , False)
  5. f = Split(fd, "\")
  6. fs = f(UBound(f))
  7. If InStr(fs, "IPQC") = 0 Then MsgBox "Àɮ׿ï¨ú¤£¬OIPQCÀÉ" & Chr(10) & "½Ð­«·s¿ï¾Ü"
  8. Loop While InStr(fs, "IPQC") = 0
  9. fc = Replace(fd, "IPQC.xls", "FQC.xls")
  10. With Workbooks.Open(fc)
  11. With Workbooks.Open(fd)
  12.    With .Sheets("Transfer")
  13.       Lot1 = Split(.[G2], "-")(0) & "-FQC3"
  14.       Lot2 = Split(.[G2], "-")(0) & "-FQC2"
  15.       y = Year(.[C1])
  16.       d = CDate(Format(.[C1], "m/d"))
  17.       m = .[L4]
  18.       Ay(0) = Array(Lot1, y, d, m)
  19.       Ay(1) = Array(Lot2, y, d, m)
  20.       Dim ar(0 To 1, 0 To 34)
  21.       For Each A In .[F4:F15]
  22.         For i = 0 To 2
  23.             ar(0, s) = A.Offset(, i).Value
  24.             ar(1, s) = A.Offset(, i + 3).Value
  25.             s = s + 1
  26.             If s = 35 Then GoTo 10
  27.         Next
  28.       Next
  29. 10
  30.     End With
  31.     .Close 0
  32. End With
  33. With .Sheets("Input")
  34.   Set C = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
  35.   C.Resize(2, 4) = Application.Transpose(Application.Transpose(Ay))
  36.   C.Offset(, 4).Resize(2, 35) = ar
  37. End With
  38. End With
  39. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 14# Hsieh

ª©¥D§A¦n,
·PÁÂÀ°¦£, §Ú¦³¸Õ¹L,
1.¨S¦³­«½Æ§å¸¹½T»{,
2.¨C¶Ç¤@¦¸¸ê®Æ, ¨Ï¥ÎªÌÁٻݭn¶}±ÒIPQC ÀÉ®×, ¬O§_¦³§ó¤è«K¤è¦¡, ÁÂÁÂ!
µù: IPQC & FQC Àɮ׳£¬O©ñ¦P¤@¸ê®Æ§¨¤U

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-4-19 16:10 ½s¿è

¦^´_ 15# dea172


IPQC-FQC.rar (167 KB)


³o­ÓFQCÀɦW¦Û°Ê»PIPQC¤Ç°t:
IPQC-FQC-2.rar (168.17 KB)

TOP

¦^´_ 16# ­ã´£³¡ªL

ª©¥D§A¦n,
´ú¸Õ«áOK, ¤£¦n·N«ä, ¦ý§ÚÁÙ¦³¤@­Ó°ÝÃD·Q½Ð±Ð, ¦A¦¸³Â·Ð¤F, ÁÂÁÂ
­Y·Q¸õ¹L IPQCÀɮתº P Äæ¦ì, ½Æ»s®É¤£±NPÄæ¦ì½Æ»s¨ìFQC¤º, ¬O§_¦³µ{¦¡¥i¨Ï¥Î?

TOP

¦^´_ 17# dea172

For i = 0 To UBound(Arr) - 1
    If Arr(i + 1, 1) = "P" Then
       '³o¸ÌªÅ¥Õ§Y¥i(¤£°µ¥ô¦ó°Ê§@)
    ElseIf Arr(i + 1, 1) = "M" Then
       For j = 5 To 6: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j '¥u§ì«e2®æ
     Else
       For j = 5 To 7: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j '§ì«e3®æ
           For j = 5 To 6: Brr(2, i * 3 + j) = Arr(i + 1, j + 4): Next j '§ì«á2®æ
    End If
Next i

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-4-19 16:45 ½s¿è

¦^´_ 17# dea172

­Y¬O­n±Æ°£¦h­Ó:
If InStr("-A2-H-P-", "-" & Trim(Arr(i + 1, 1)) & "-") Then

A2,H,P ³£¤£¿ý¤J

¦]¬°Àɮפ¤A2«á¦h¤F¤@­ÓªÅ®æ, ¶·¥ÎTrim²M°£

°O¦í¡G³o­Ó±Æ°£­n©ñ¦bIF±ø¥óªº²Ä¤@­Ó¡A¨ä¥¦¦A¥ÎELSEIF±µ¨ä¥¦±ø¥ó

TOP

¬°¤F¨¾§b¡A¦bµ{¦¡½Xªº«eºÝ¦A¥[³o¤T¦æ¡G
If IsDate([C1]) = False Then MsgBox "¤é´Á®æ¦¡¿ù»~©Î¥¼¿é¤J!!": Exit Sub
If [E1] = "" Then MsgBox "Machine No ¥¼¿é¤J!!": Exit Sub
If [G1] Like "########-####" = False Then MsgBox "Lot No ¿ù»~©Î¥¼¿é¤J!!": Exit Sub

TOP

        ÀR«ä¦Û¦b : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD