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

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

¨Ï¥Îvba ½Æ»s°ÝÃD

¬O§_¥i¹ê²{¡A±qIPQC¼Æ­È¡A
­Y¦³¤­/¤»µ§¸ê®Æ³z¹L«ö¶s½Æ»s¨ìFQC¡]¦p¹Ï¤@¡^¡A§å¸¹-FQC +¸ê®Æ¼Æ­È
§å¸¹-FQC3
§å¸¹-FQC2
­Y¦³¥|µ§¸ê®Æ³z¹L«ö¶s½Æ»s¨ìFQC¡]¦p¹Ï¤@¡^¡A§å¸¹-FQC +¸ê®Æ¼Æ­È
§å¸¹-FQC3
§å¸¹-FQC1
­Y¦³¤Tµ§¸ê®Æ³z¹L«ö¶s½Æ»s¨ìFQC¡A§å¸¹-FQC +¸ê®Æ¼Æ­È
§å¸¹-FQC3
­Y¦³¤Gµ§¸ê®Æ³z¹L«ö¶s½Æ»s¨ìFQC¡A§å¸¹-FQC +¸ê®Æ¼Æ­È
§å¸¹-FQC2
­Y¦³¤@µ§¸ê®Æ³z¹L«ö¶s½Æ»s¨ìFQC¡A§å¸¹-FQC +¸ê®Æ¼Æ­È
§å¸¹-FQC1
³Â·Ð¦U¦ì¤j®vÀ°¦£¡I



VBA-Test.zip (425.36 KB)

TOP

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

VBA ½Æ»sdata°ÝÃD

1.§å¸¹§ì¨ú¨Ó·½
»¡©ú: ±qIPQC¤ºªº G2 Äæ¦ì±N§å¸¹½Æ»s¨ìA6/A7, ©T©w±N A6(§å¸¹-FQC3)/A7(§å¸¹-FQC2)

------------------------------------------------------------------------------------
2.¤é´Á§ì¨údata ¨Ó·½
»¡©ú: ª½±µ§ì¨úIPQC C1Äæ¦ì

------------------------------------------------------------------------------------
3.¾÷¥xÃ丹§ì¨ú¨Ó·½
»¡©ú: §ì¨úIPQC L4Äæ¦ì

------------------------------------------------------------------------------------
4.A1 ~ P data §ì¨ú¨Ó·½
»¡©ú:
­Y¼Æ­È¦³¤Tµ§®É
IPQC¤º«e¤Tµ§¼Æ¾Ú½Æ»s¨ìFQC3 ¼Æ­È¤º ¡]¦p¹Ï¤@¡^
§å¸¹-FQC3  «e¤Tµ§¼Æ­È
IPQC¤º«á¨âµ§¼Æ¾Ú½Æ»s¨ìFQC2 ¼Æ­È¤º ¡]¦p¹Ï¤@¡^
§å¸¹-FQC2  «á¨âµ§¼Æ­È
¹Ï¤@

­Y¼Æ­È¦³¥|µ§®É
IPQC¤º«e¤Tµ§¼Æ¾Ú½Æ»s¨ìFQC3 ¼Æ­È¤º ¡]¦p¹Ï¤G¡^
§å¸¹-FQC3  «e¤Tµ§¼Æ­È
IPQC¤º«á¨âµ§¼Æ¾Ú½Æ»s¨ìFQC2 ¼Æ­È¤º ¡]¦p¹Ï¤G¡^
§å¸¹-FQC2  «á¨âµ§¼Æ­È
¹Ï¤G

------------------------------------------------------------------------------------
5.M1 data §ì¨ú¨Ó·½
»¡©ú:
ª½±µ±NIPQC ¤ºF15/G15 ¼Æ­È½Æ»s¨ì FQC¤ºªºAL6/AM6¤º


FQC ¸ê®Æ»Ý²Ö¿n¤U¥h, ¤£¥i²M°£

VBA-Test.zip (89.84 KB)

  1. Sub TEST()
  2. Dim Arr, Brr(1 To 2, 1 To 39), i&, j%, xE As Range
  3. Arr = [A4:J15]
  4. Brr(1, 1) = Split([G2], "-")(0) & "-FQC3"
  5. Brr(2, 1) = Split([G2], "-")(0) & "-FQC2"
  6. Brr(1, 2) = Year([C1]): Brr(2, 2) = Year([C1])
  7. Brr(1, 3) = [C1]: Brr(2, 3) = [C1]
  8. Brr(1, 4) = [L4]: Brr(2, 4) = [L4]
  9. For i = 0 To UBound(Arr) - 1
  10.     If Arr(i + 1, 1) <> "M1" Then
  11.        For j = 5 To 7: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j
  12.        For j = 5 To 6: Brr(2, i * 3 + j) = Arr(i + 1, j + 4): Next j
  13.     Else
  14.        For j = 5 To 6: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j
  15.     End If
  16. Next i
  17. Set xE = Workbooks("FQC").Sheets("input").[A65536].End(xlUp)(2)
  18. If xE.Row < 6 Then Set xE = xE(2)
  19. xE.Resize(2, 39) = Brr
  20. End Sub
½Æ»s¥N½X

TOP

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

§A¦n,
°õ¦æµ{¦¡«áµo²{¦³¿ù»~, §Ú¦³¹Á¸Õ­×§ïµ{¦¡, ¦ý¬O¤´µMµLªk¨Ï¥Î, ¥i§_¨ó§UÀ°¦£¬Ý¤@¤U, ÁÂÁÂ
-----------------------------------------------------------------------------
­ì©lµ{¦¡
Set xE = Workbooks("FQC").Sheets("Input").[A65536].End(xlUp)(2)
§Ú¦³¹Á¸Õ­×§ïµ{¦¡¬°
Set xE = Workbooks("FQC").Sheets("Sheet1").[A65536].End(xlUp)(2)
-----------------------------------------------------------------------------

TOP

¦^´_ 4# dea172

³oÀ³¸Ó¬O¦]¬°§A¨S¦³¦P®É§â³o¨â­ÓÀÉ®×¥´¶}¡A¦pªG§A·Q¥u¥´¶}IPQC´N°õ¦æµ{¦¡ªº¸Ü
  1. Sub TEST()
  2. Dim Arr, Brr(1 To 2, 1 To 39), i&, j%, xE As Range
  3. Arr = [A4:J15]
  4. Brr(1, 1) = Split([G2], "-")(0) & "-FQC3"
  5. Brr(2, 1) = Split([G2], "-")(0) & "-FQC2"
  6. Brr(1, 2) = Year([C1]): Brr(2, 2) = Year([C1])
  7. Brr(1, 3) = [C1]: Brr(2, 3) = [C1]
  8. Brr(1, 4) = [L4]: Brr(2, 4) = [L4]
  9. For i = 0 To UBound(Arr) - 1
  10.     If Arr(i + 1, 1) <> "M1" Then
  11.        For j = 5 To 7: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j
  12.        For j = 5 To 6: Brr(2, i * 3 + j) = Arr(i + 1, j + 4): Next j
  13.     Else
  14.        For j = 5 To 6: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j
  15.     End If
  16. Next i
  17. Workbooks.Open Filename:="C:\Users\User\Downloads\VBA-Test\FQC.xls"
  18. Set xE = Workbooks("FQC").Sheets("input").[A65536].End(xlUp)(2)
  19. If xE.Row < 6 Then Set xE = xE(2)
  20. xE.Resize(2, 39) = Brr

  21. Workbooks("FQC").Close 1
  22. End Sub
½Æ»s¥N½X
§âWorkbooks.Open Filename§ï¦¨§Aªº¸ô®|´N¦n
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 4# dea172


Dim xB As Workbook
On Error Resume Next '¥H¤U¤T¦æ¥i¥HÀˬdFQC¬O§_¶}±Ò¤¤
Set xB = Workbooks("FQC")
On Error GoTo 0

If xB Is Nothing Then Set xB = Workbooks.Open(ThisWorkbook.Path & "\FQC.xls") '­Y¥¼¶}±Ò,°õ¦æ¶}±ÒÀÉ®×(ÁקK­«Âж}±Ò¦Ó·í¾÷)
Set xE = xB.Sheets("input").[A65536].End(xlUp)(2)
If xE.Row < 6 Then Set xE = xE(2)
xE.Resize(2, 39) = Brr
xB.Close 1  'Ãö³¬FQC, ¨Ã¦sÀÉ

TOP

¥»©«³Ì«á¥Ñ dea172 ©ó 2018-4-18 14:34 ½s¿è

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

Dear ª©¥D,
·PÁ§ֳt¦^´_, ¦ý§Ú´ú¸Õ«á, ¤´¦³¥H¤U´XÂI°ÝÃD, ·Q¦A¦¸³Â·Ð§A, ÁÂÁÂ!
1.§Ú¦³§ó§ï¤¶­±«á, «ö¤UUpload ¥X²{Error message, ¬O§_»Ý­n­×§ï¤°»ò¦a¤è?

2.­YFQC Àɮ׫᭱¨â­Ó¸ê®Æ¥u­n©T©w§ì¨ú«e¨âµ§¸ê®Æ, ¦p¦ó­×§ïµ{¦¡?

3.­YFQC Àɮ׸ê®Æ¥u­n©T©w§ì¨ú5µ§¸ê®Æ, ¦p¦ó­×§ïµ{¦¡?

4.¬O§_¦³¾÷·|¥i¥H·s¼W­Y±qIPQC¶Ç¸ê®Æ¦ÜFQC¤º, §å¸¹­«½Æ·|µLªk¤W¶Ç

VBA ¸ê®Æ´ú¸Õ.zip (434.92 KB)

TOP

¦^´_ 7# dea172
  1. Private Sub CommandButton2_Click()
  2. Dim Arr, Brr, i&, j%, xE As Range
  3. Arr = [A3:J17]
  4. ReDim Brr(1 To 2, 1 To [A:AV].Columns.Count)
  5. Brr(1, 1) = Split([G1], "-")(0) & "-FQC3"
  6. Brr(2, 1) = Split([G1], "-")(0) & "-FQC2"
  7. Brr(1, 2) = Year([C1]): Brr(2, 2) = Year([C1])
  8. Brr(1, 3) = [C1]: Brr(2, 3) = [C1]
  9. Brr(1, 4) = [E1]: Brr(2, 4) = [L2]
  10. For i = 0 To UBound(Arr) - 1
  11.     If i >= UBound(Arr) - 2 Then '³Ì«á¨âµ§(L/M)
  12.        For j = 5 To 6: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j '¥u§ì«e2®æ
  13.     Else
  14.        For j = 5 To 7: Brr(1, i * 3 + j) = Arr(i + 1, j + 1): Next j '§ì«e3®æ
  15.        For j = 5 To 6: Brr(2, i * 3 + j) = Arr(i + 1, j + 4): Next j '§ì«á2®æ
  16.     End If
  17. Next i

  18. Dim xN$, xB As Workbook, xS As Worksheet, xF As Range
  19. xN = "L-15-3-2018-FQC.xls"
  20. On Error Resume Next '¥H¤U¤T¦æ¥i¥HÀˬdFQC¬O§_¶}±Ò¤¤
  21. Set xB = Workbooks(xN)
  22. If xB Is Nothing Then Set xB = Workbooks.Open(ThisWorkbook.Path & "\" & xN) '­Y¥¼¶}±Ò,°õ¦æ¶}±ÒÀÉ®×
  23. On Error GoTo 0
  24. If xB Is Nothing Then MsgBox "§ä¤£¨ì¡e" & xN & "¡fÀÉ®×": Exit Sub

  25. Set xS = xB.Sheets("¿é¤Jªí")
  26. Set xF = xS.[A:A].Find(Split(Brr(1, 1), "-")(0), Lookat:=xlPart)
  27. If Not xF Is Nothing Then MsgBox "§å¸¹­«ÂÐ": xB.Close 0: Exit Sub
  28. Set xE = xS.[A65536].End(xlUp)(2)
  29. If xE.Row < 6 Then Set xE = xE(2)
  30. xE.Resize(2, UBound(Brr, 2)) = Brr
  31. xB.Close 1  'Ãö³¬FQC, ¨Ã¦sÀÉ
  32. End Sub
½Æ»s¥N½X
L-15-3-2018-IPQC.rar (75.84 KB)

TOP

¥»©«³Ì«á¥Ñ dea172 ©ó 2018-4-19 09:19 ½s¿è

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

§A¦n,
ÁÂÁª©¥D©âªÅÀ°¦£, §Ú¦³¨Ï¥Î¥H¤Wµ{¦¡, ¤´µMµLªk¥¿±`°õ¦æ, §ÚÁÙ¬O·s¤â, ¤´¦³¥H¤U°ÝÃD, ³Â·Ð¤F

§ÚÁ`¦@¦³¤T­ÓÀÉ®×, ¤À§O¬°¥H¤U :
1.IPQC ÀÉ®× HÄæ¦ì¦³¤­­Ó¼Æ­È, FQC §ì¨ú¤­­Ó¼Æ­È (°Ñ閲ÀÉ®× CUP -1070)


2.IPQC ÀÉ®× HÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¥u§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-15-3)


2.IPQC ÀÉ®× P/MÄæ¦ì¦³¤­­Ó¼Æ­È, FQC ¦U§ì¨ú«e¨â­Ó¼Æ­È(°Ñ閲ÀÉ®×L-26P)

CUP 1070.zip (56.28 KB)

L-15-3.zip (63.61 KB)

L-26P.zip (89.09 KB)

TOP

¦^´_ 9# dea172

°ÝÃD´y­z¤£²M¡A¥»¨Ó¥u¦³¤@¹ï¤@ÀɮסA²{Åܦ¨¤T­Ó¡AÃø¥H¤U¤â¡I¡I¡I
¢°¡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"
¢±¡DIPQCªº¢ÏÄ涵¥Ø¼Æ¶q¬O¡e©T©w¡fªº¡H¡@¥B¥²»PFQC¬Û¹ïÀ³¡H
¢²¡D§ì¢´®æ©Î§ì¢±®æªº³W«h¬O¤°»ò¡H
¡@¡@©ÎªÌ¡A¥i§Q¥ÎIPQCªº¢ÙÄæ¡A§ì¢´µ§ªº¿é¤J¢´¡A§ì¢±µ§ªº¿é¤J¢±¡A´N¥Î³o¨Ó§PÂ_§ì´X®æ

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD