Scott090

¦^´_ 16# Airman


    ¬O§_¥i§iª¾¹ê»Ú¤WŲ§O¿ëÃѤF´X¦Ê­ÓÀɮסA¥Î¤F¦h¤Ö®É¶¡©O?

Airman

¦^´_ 13# Scott090
Scott090¤j¤j:±z¦n!
¤£¦n·N«ä¡A¨S¦³ª`·N¨ì±zµ{¦¡¼g¦b¼Ò²Õ1^^"
¦b°õ¦æÀɲK¥[Main
¤w¥i¥¿½T°õ¦æ¡C

°õ¦æ«á~µª®×¥¿½T~´ú¸Õ¦¨¥\~·P®¦^^

¯S§O·PÁ±zªº¤å¦rµù¸Ñ^^

Airman

¦^´_ 13# Scott090
Scott090¤j¤j:±z¦n!
¤£¦n·N«ä¡A2­Ó"²Î­p"Àɬ°2007ª©µLªk¶}±Ò®Ö¹ïµª®×¡F
¥Dµ{¦¡ÀÉÂI°õ¦æÁä~¨S¦³¤ÏÀ³^^"

Airman

¦^´_ 12# ­ã´£³¡ªL
­ã¤j:±z¦n!
´ú¸Õ¦¨¥\~·P®¦^^

±z»¡ªº¨S¦³¿ù~
' If R < 10 Then xB.Close 0: GoTo 101
§ï¬°
If R < 2 Then xB.Close 0: GoTo 101  '·Ó¼ÒÀÀÀ³¸Ó¥Î³o¤@¦æ
µª®×¤~¬O¥¿½Tªº¡CÁÂÁ±z^^

Scott090

¦^´_ 11# Airman

   ¸Õ¬Ý¬Ý¬O§_²Å¦X»Ý¨D
          ¤µ¤éÁ`ªí(§¡­È±Æ§Ç).rar (90.38 KB)

¤U¸ü¦¸¼Æ: 4

2019-5-4 21:42

­ã´£³¡ªL

Private Sub CommandButton1_Click()
Dim BK As Workbook, F$, T$, xB As Workbook, xS As Worksheet
Dim xD, Arr(1 To 7, 1 To 49), Brr, R&, V, xR As Range
Set BK = ThisWorkbook
Set xD = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Do
  If F = "" Then F = Dir(BK.Path & "\*.xls") Else F = Dir()
  If F = "" Then Exit Do
  If Not F Like "¤µ¤éÁ`ªí(§¡­È±Æ§Ç)-*_*-(####-##-##).xls" Then GoTo 101
  T = Left(Right(F, 16), 12)
  Brr = xD(T)
  If Not IsArray(Brr) Then xD(T) = Arr:    Brr = Arr
  '-----------------------------------------------
  Set xB = Workbooks.Open(BK.Path & "\" & F, ReadOnly:=True): Set xS = xB.Sheets(1)
  R = xS.[A65536].End(xlUp).Row - 8
  If R < 10 Then xB.Close 0: GoTo 101
  'If R < 2 Then xB.Close 0: GoTo 101  '·Ó¼ÒÀÀÀ³¸Ó¥Î³o¤@¦æ
  For Each xR In xS.Cells(R, 2).Resize(1, 49)
      V = InStr("---8--37-38-4--45-39-40-", "-" & xR.Interior.ColorIndex & "-") / 3
      If Val(xR) > 0 And V > 0 Then Brr(V, xR) = Brr(V, xR) + 1
  Next
  xD(T) = Brr: xB.Close 0
101: Loop
'----------------------------------------------------
If xD.Count = 0 Then Exit Sub
Application.DisplayAlerts = False
For Each V In xD.keys
    F = "¤µ¤éÁ`ªí(§¡­È±Æ§Ç)-" & V & "_²Î­p.xls"
    BK.Sheets("Sample").Copy
    With ActiveWorkbook
         .Sheets(1).[B2].Resize(7, 49).Value = xD(V & "")
         .SaveAs Filename:=BK.Path & "\" & F, CreateBackup:=False
         .Close 0
    End With
Next
End Sub

»P¼ÒÀÀµ²ªG¤£¦P, ¶È¥H "Àx¦s®æ©³¦â" ¤Î "¼Æ¦r" ¬°§P§O,
¤µ¤éÁ`ªí(§¡­È±Æ§Ç).rar (71.11 KB)

¤U¸ü¦¸¼Æ: 3

2019-5-4 17:38

Airman

¦^´_ 10# Scott090
Scott090¤j¤j:±z¦n!
1. ÀÉ®×¥H ¤é´Á§Ç °µ¤ÀÃþ¡F »P  X-Y µLÃö ==> OK! 8#ªº¸É¥R»¡©ú~½Ð"©¿²¤"§Y¥i¡C
2. ¦P¤@¤ÀÃþ¤º¨C¤@­ÓÀɮ׳£°µ¬Û¦Pªº²Î­p³W«h¡A
     ²Î­p³W«h¡G
                       ..........                     
                ..........
²Î­p³W«h¡G¤£¹ï!

¤U¸ü (55.24 KB)

2019-5-4 14:19

¤U¸ü (63.94 KB)

2019-5-4 14:20

¤U¸ü (62.18 KB)

2019-5-4 14:20

³W«h­«ÂI¾ã²z~ÂԽаѦÒ~
²Î­p½d³ò¡G
1_Àɮפº¤u§@ªíªºCells([B65536].End(xlUp).Row) <10(§Y³Ì«á¦³¼Æ¦rªº¦C¼Æ¤p©ó²Ä10¦C)ªºÀɮײ¤¹L¤£­p¡C
2_·j´M²Î­pªº½d³ò±q²Ä2¦C(§Y²Ä¤@¦C¼ÐÃDªº¼Æ¦r¤£¦b­pºâ½d³ò)¨ìCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)¡C

²Î­p³W«h¡G
³æ¤@­Ó(§Y¦³¼Ð¥Ü40¸¹©³¦â)¬O«ü¡JÀɮפºªº¤u§@ªí¤§
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)¦³Åã¥Ü¼Æ¦r(§Y<>"")¡A
¦ýCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¨S¦³¼Æ¦r(§Y="")
©Î
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)=²Ä2¦C®É¡A¬YÄ榳Åã¥Ü¼Æ¦r(§Y<>"")¡A

³s2­Ó(§Y¦³¼Ð¥Ü39¸¹©³¦â)¬O«ü¡JÀɮפºªº¤u§@ªí¤§
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)¦³Åã¥Ü¼Æ¦r(§Y<>"")¡A
¥BCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¦ýCells([B65536].End(xlUp).Row - 10, 2).Resize(1, 49)ªº¦PÄæ¨S¦³¼Æ¦r(§Y="")
©Î
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)=²Ä3¦C®É¡A¬YÄ榳Åã¥Ü¼Æ¦r(§Y<>"")¡A
¥BCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")

³s3­Ó(§Y¦³¼Ð¥Ü45¸¹©³¦â)¬O«ü¡JÀɮפºªº¤u§@ªí¤§
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)¦³Åã¥Ü¼Æ¦r(§Y<>"")¡A
¥BCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 10, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¦ýCells([B65536].End(xlUp).Row - 11, 2).Resize(1, 49)ªº¦PÄæ¨S¦³¼Æ¦r(§Y="")
©Î
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)=²Ä4¦C®É¡A¬YÄ榳Åã¥Ü¼Æ¦r(§Y<>"")¡A
¥BCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 10, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")

¨ä¾l³s4­Ó¡A....¡A³s6­Ó¥H¦¹Ãþ±À¡C

³s7­Ó(§Y¦³¼Ð¥Ü8¸¹©³¦â)¬O«ü¡JÀɮפºªº¤u§@ªí¤§
·íCells([B65536].End(xlUp).Row - 8, 2).Resize(1, 49)¦³Åã¥Ü¼Æ¦r(§Y<>"")¡A
¥BCells([B65536].End(xlUp).Row - 9, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 10, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 11, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 12, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 13, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")
¥BCells([B65536].End(xlUp).Row - 14, 2).Resize(1, 49)ªº¦PÄæ¤]¦³¼Æ¦r(§Y<>"")

Scott090

¦^´_ 8# Airman


   

¤U¸ü (185.95 KB)

2019-5-4 13:33

   
    ³o¬O§Ú°µªºµª®×¡A½Ð½T»{

Scott090

¦^´_ 8# Airman

     1. ÀÉ®×¥H ¤é´Á§Ç °µ¤ÀÃþ¡F »P  X-Y µLÃö
      2. ¦P¤@¤ÀÃþ¤º¨C¤@­ÓÀɮ׳£°µ¬Û¦Pªº²Î­p³W«h¡A
     ²Î­p³W«h¡G
                      ³æ¤@­Ó¬O±q (1~49)¡A
                      ³s2­Ó¬O±q (2~49)¡A
                      ³s3­Ó¬O±q (3~49)¡A
                       ..........
                      ³sx­Ó¬O±q (x~49)¡A
                      ³s7­Ó¬O±q (7~49) ­pºâªº¡C
                     
      ¨Ì¾Ú²Î­p³W«h¡A 02¡B03Äæ ¤£¸Ó¦³ ³s6­Óªº¼Æ¦r¡A 06Äæ ¤£¸Ó¦³ ³s7­Óªº¼Æ¦r
       ½Ð¦b½T»{³W«h¡C

Airman

¦^´_ 7# Scott090

¤U¸ü (11.16 KB)

2019-5-4 11:25

Scott090¤j¤j:±z¦n!
¨«¦ì¦³ÂIÄY­«~¥HÁ`¼Æ­p¡J¡i01~06¦@9­Ó¡jÀ³¸Ó¨S¦³¿ù^^

¥t¥~~¬õ¦â¦rªº½s½X¡A¤p§Ì§@­Ó¸É¥R¡J
X_Y
X=¸¹½X±Æ§Ç=1~49(©Ò¥H¤@­Ó¤é´Á§O¦³49­ÓÀÉ®×)
Y=´Á¶Z=¦³_³s1¡F_³s2¡F... ¡F_³s10¡F_1¡F_2¡F.... ¡F_99¡F_100µ¥¤£©wªº½s½X¡A(©Ò¥H¤@­Ó¤é´Á§O¦³49(§t)­Ó¥H¤º(§Y·|¦³­«½Æ)ªº´Á¶Z½s½X)¡C

¥H¤WÂԨѰѦÒ!     ÁÂÁ±z^^