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

[µo°Ý] §PŪ&¶ñ¤J¦¸¼Æªº»yªk¡C

[µo°Ý] §PŪ&¶ñ¤J¦¸¼Æªº»yªk¡C

¥»©«³Ì«á¥Ñ Airman ©ó 2019-10-7 10:45 ½s¿è

·Q±N¥DÀɵ{¦¡½X¦C20~¦C23§ï¬°¦p¤U¦Cªº»Ý¨D~
»Ý¨D¡J
1_§PŪ¨C¤@­Ó´ú¸ÕÀɪº"ªÅ¼Æ¦C([A65536].End(xlUp).Row - 6)"¤§³Ì¤j¼Æ­È(§Y¦³¼Ð¥Ü38¸¹©³¦âªº¼Æ¦r)¡A
¨Ã¨Ì§Ç¶ñ¤J"49_¤µ¤éÁ`ªí-(####-##-##)_ªÅ¼Æ²Î­pªí"ªº¦P¸¹½X"ªÅ¼Æ¦C³Ì¤j¦¸¼Æ"¤§Àx¦s®æ¡F
µM«á±N¨C¤@­Ó´ú¸ÕÀɪº¹ïÀ³"¾÷¼Æ¦C([A65536].End(xlUp).Row - 4)"ªº¼Æ¦r¡A¶ñ¤J¦PÄ椧¤U¤@­ÓªºÀx¦s®æ¡C
PS¡J¨C¤@²Õ¼Æ¦r¡A¶¡¹j¤@ªÅ¥Õ¦C¡C

2_·í¹ïÀ³ªº"¾÷¼Æ¦C"¤§¼Æ¦r¬O¬°¸Ó¦C³Ì¤p¼Æ­È(§Y¦³¼Ð¥Ü40¸¹©³¦âªº¼Æ¦r)®É¡A
«h±N¶ñ¤J¸Ó¼Æ¦rªº"49_¤µ¤éÁ`ªí-(####-##-##)_ªÅ¼Æ²Î­pªí"¤§Àx¦s®æ¼Ð¥Ü40¸¹©³¦â¡C

3_·í²Ä2¶µ¦³¼Ð¥Ü40¸¹©³¦âªº¼Æ¦r=0®É¡A«h¦A±N¸Ó¼Æ¦r¼Ð¥Ü3¸¹¦rÃC¡C

¥H¤W»Ý¨D·q½Ð¦U¦ì¤j¤j´f¤©½ç±Ð!ÁÂÁÂ!
½d¨ÒªþÀÉ :   49_¤µ¤éÁ`ªí(ªÅ¼Æ²Î­p).rar (73.15 KB)

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, tim!
tim = Timer
[L1] = ""
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 "49_§¡­È±Æ§Ç-¤µ¤éÁ`ªí-*_*-(####-##-##).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 < 12 Then xB.Close 0: GoTo 101  'Á`¦C¼Æ¤p©ó20,GoTo 101.
'  For Each xR In xS.Cells(R, 2).Resize(1, 49)   '¦C20
'      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       '¦C23
  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 = "49_¤µ¤éÁ`ªí-" & V & "_ªÅ¼Æ²Î­pªí.xls"
    BK.Sheets("Sample").Copy
    With ActiveWorkbook
         .Sheets(1).[B2].Resize(7, 49).Value = xD(V & "")
         d$ = Format(Mid(V, 2, 10), "yyyy/m/d")
         Set xR = Nothing
         Set xR = BK.Sheets("DATA").[A:A].Find(d, Lookat:=xlPart)
         If Not xR Is Nothing Then
            For i = 4 To 9
               .Sheets(1).Cells(1, xR(1, i) + 1).Font.ColorIndex = 10
               .Sheets(1).Cells(1, xR(2, i) + 1).Interior.ColorIndex = 6
            Next i
            Sheets(1).Cells(1, xR(1, 10) + 1).Font.ColorIndex = 7
            Sheets(1).Cells(1, xR(2, 10) + 1).Interior.ColorIndex = 8
         End If
         .SaveAs Filename:=BK.Path & "\" & F, CreateBackup:=False
         .Close 0
    End With
Next
[L1] = Format((Timer - tim) / 24 / 60 / 60, "hh:mm:ss")
End Sub

TOP

¦^´_ 2# Airman
2¼Óªºµ{¦¡½X·½¦Û~
http://forum.twbts.com/viewthrea ... a=pageD1&page=2
12¼Ó©M32¼Óªºµ{¦¡½X¡C
ÂԨѰѦÒ!ÁÂÁÂ!

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2019-10-18 00:11 ½s¿è

¦^´_ 1# Airman
49_¤µ¤éÁ`ªí(ªÅ¼Æ²Î­p)_V2.rar (74.4 KB)
­«­z»Ý¨D¡J
¦]¬°±NSampleªº®æ¦¡§ó§ï¬°¦pªþ¥óªº¥DÀÉ"Sample"¤u§@ªí¡A
©Ò¥H§ó§ï¬ÛÃöªº¹ïÀ³»Ý¨D¦p¤U~
1_±N­ì·j´M¨C¤@­Ó§PŪ´ú¸ÕÀɪºRange("B2"&[A65536].End(xlUp).Row - 8, "AX2"&[A65536].End(xlUp).Row - 14)"½d³ò¡A
§ï¬°
Range("B2"&[A65536].End(xlUp).Row - 6, "AX2"&[A65536].End(xlUp).Row - 6)  =>§Y"ªÅ¼Æ"¦C¡C

2_±N­ì·j´M¨C¤@­Ó§PŪ´ú¸ÕÀɪº¥Ø¼Ð­È¤§ÅÞ¿è±ø¥ó
§ï¬°
·j´M"ªÅ¼Æ"¦Cªº³Ì¤j­È  =>§Y¦³¼Ð¥Ü"38¸¹"©³¦âªº¼Æ¦r¡F

3_±N¤W­z"ªÅ¼Æ"¦Cªº³Ì¤j­È¨Ì§Ç¶ñ¤J"49_¤µ¤éÁ`ªí-(####-##-##)_ªÅ¼Æ²Î­pªí"ªº¦P¸¹½X"ªÅ¼Æ¦C³Ì¤j¦¸¼Æ"(²Ä2¦C)¤§Àx¦s®æ¡F
µM«á±N¨C¤@­Ó´ú¸ÕÀɪºRange("B2"&[A65536].End(xlUp).Row - 4, "AX2"&[A65536].End(xlUp).Row - 4)¤§¹ïÀ³"¾÷¼Æ"¦Cªº¼Æ¦r¡A¶ñ¤J¦PÄ椧¤U¤@­Ó(²Ä3¦C)ªºÀx¦s®æ¡C

¦pªG¦³¬Y­Ó¸¹½X¦³2²Õ(§t)¥H¤Wªº¼Æ­È®É¡A«h¶¡¹j¤@¦CÄ~Äò¶ñ¤J¡C

4_·í¹ïÀ³ªº"¾÷¼Æ¦C"¤§¼Æ¦r¬O¬°¸Ó´ú¸ÕÀɾ÷¼Æ¦Cªº³Ì¤p¼Æ­È(§Y¦³¼Ð¥Ü40¸¹©³¦âªº¼Æ¦r)®É¡A
«h±N¶ñ¤J¸Ó¼Æ¦rªº"49_¤µ¤éÁ`ªí-(####-##-##)_ªÅ¼Æ²Î­pªí"¤§Àx¦s®æ¼Ð¥Ü40¸¹©³¦â¡C

5_·í²Ä4¶µ¦³¼Ð¥Ü40¸¹©³¦âªº¼Æ¦r=0®É¡A«h¦A±N¸Ó¼Æ¦r¼Ð¥Ü3¸¹¦rÃC¡C

¥H¤W»Ý¨D·q½Ð¦U¦ì¤j¤j´f¤©½ç±Ð!ÁÂÁÂ!

TOP

¦^´_ 4# Airman

²´¤O¤j¤£¦p«e, ¬Ý¤Ó¦h¸ê®Æ¤Î¤å¦r¤Ó¦Y¤O,
²{¦b­Y¦³Â²³æªºÃD, ÁÙ¥i¸Ñµª¤@¤G§a~~

TOP

¦^´_ 5# ­ã´£³¡ªL
­ã¤j:
·PÁ¦^À³^^
¦pªG¬O³o¼Ë¡A¨º³o¤@ÃD´N·í§@¬OÀ°¤p§Ìªº"³Ì«á¤@¦¸¦£"~·P®¦^^"

TOP

¨SÅçÃÒ:
49_¤µ¤éÁ`ªí(ªÅ¼Æ²Î­p)_V2.rar (82.67 KB)

TOP

¦^´_ 7# ­ã´£³¡ªL
1021_ªÅ¼Æ²Î­pªí.rar (95.42 KB)
­ã¤j¡J
·PÁ¦^ÂÐ^^

¤£¦n·N«ä¡A¥H¤é´Á§O¤À¿ë¡A²£¥Í¦h­Ó¤é´Áªº®ÄªGÀɤ§»Ý¨D®Ä¯à¤£¨£¤F¡A¥i§_½Ð±z¦A¸É¥R½ç¥¿?
EX¡J½d¨Òªþ¥ó¤¤ªº"49_¤µ¤éÁ`ªí-(2019-09-20)_ªÅ¼Æ²Î­pªí"¶]¤£¥X¨Ó¡A¥u¯à¶]¥X"49_¤µ¤éÁ`ªí-(2019-10-01)_ªÅ¼Æ²Î­pªí"¡C
ÁÂÁ±z!

TOP

¦^´_ 8# Airman


¤µ¤é¦³ÂIªÅ, ¤@¾v°Ê¥þ¨­!!!!
49_¤µ¤éÁ`ªí(ªÅ¼Æ²Î­p)_V3.rar (103.29 KB)

TOP

¦^´_ 9# ­ã´£³¡ªL
­ã¤j :
¤£¦n·N«ä¡A¦]¬°¬O´£°ÝÀɭ쥻´N¦³ªº®Ä¯à¡A©Ò¥H¨S¦³¦A¯S§O»¡©ú^^"

´ú¸ÕOK¤F!~·P®¦

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD