- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 253
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2024-11-22
|
¦^´_ 5# lai.k.h - Sub ¦h«Äæ¦ìÀˬd()
- Dim A As Range, Mystr$, Ar(), ErrStr()
- Set ¶}©l = CreateObject("Scripting.Dictionary")
- Set µ²§ô = CreateObject("Scripting.Dictionary")
- Set ¬P´Á = CreateObject("Scripting.Dictionary")
- Set ¤é´Á = CreateObject("Scripting.Dictionary")
- Set §Ç¸¹ = CreateObject("Scripting.Dictionary")
- With Sheet1
- .[D3:Q65536].Interior.ColorIndex = 0 'D~QÄæÃC¦â²M°£
- For Each A In .Range(.[D3], .[D65536].End(xlUp)) 'D3¶}©l°µ°j°é
- x = 0
- Mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 3))), Chr(10)) '¥Î¸õ¦æ¦r¤¸³s±µD~F¤º®e§@¯Á¤Þ¦r¦ê
- ¶}©l(Mystr) = Mid(Split(A.Offset(, 1), "-")(0), 3) 'EÄæ¸ê®Æ¥H-¤À³Î¦¨2³¡¤À¡A²Ä1³¡¤À²Ä3½X¶}©l¬°¶}©l¤é´Á
- µ²§ô(Mystr) = Split(A.Offset(, 1), "-")(1) 'EÄæ¸ê®Æ¥H-¤À³Î¦¨2³¡¤À¡A²Ä2³¡¤À¬°µ²§ô¤é´Á
- 'DÄæ¦V¥k6Ä檺¦ì¸m¦pªG¤£µ¥©ó¶}©l¤é´Á´N§ïÅÜÃC¦â¡A¨Ã±N¿ù»~¤å¦r¦s¤J°}¦C
- If Format(A.Offset(, 6), "yyyymmdd") <> ¶}©l(Mystr) Then A.Offset(, 6).Interior.ColorIndex = 36: ReDim Preserve ErrStr(x): ErrStr(x) = "¶}©l¤é´Á¿ù»~": x = x + 1
- 'DÄæ¦V¥k7Ä檺¦ì¸m¦pªG¤£µ¥©óµ²§ô¤é´Á´N§ïÅÜÃC¦â¡A¨Ã±N¿ù»~¤å¦r¦s¤J°}¦C
- If Format(A.Offset(, 7), "yyyymmdd") <> µ²§ô(Mystr) Then A.Offset(, 7).Interior.ColorIndex = 36: ReDim Preserve ErrStr(x): ErrStr(x) = "µ²§ô¤é´Á¿ù»~": x = x + 1
-
- If IsEmpty(¬P´Á(Mystr)) Then '¦pªG¥HMystr¬°¯Á¤Þªº¬P´Á¬Oªì©lÈ
- ¬P´Á(Mystr) = A.Offset(, 8) '¬ö¿ýDÄæ¦V¥k8Ä檺È
- ElseIf A.Offset(, 8) <> ¬P´Á(Mystr) Then '§_«h´N¸ò¤w¦sªºÈ¤ñ¸û¡A¦pªG¤£¦P
- 'DÄæ¦V¥k8ÄæÅܦâ¡A¨Ã±N¿ù»~¤å¦r¦s¤J°}¦C
- A.Offset(, 8).Interior.ColorIndex = 36: ReDim Preserve ErrStr(x): ErrStr(x) = "«ô³X¬P´Á´Á¿ù»~": x = x + 1
- End If
- If IsEmpty(¤é´Á(Mystr)) Then '¦pªG¥HMystr¬°¯Á¤Þªº¤é´Á¬Oªì©lÈ
- ReDim Preserve Ar(0) '¥¬¸m¤@Ó¥u¦³1Ó¤¸¯Àªº°}¦C
- Ar(0) = A.Offset(, 9) '¦¹°}¦C²Ä1Ó¤¸¯ÀȬODÄæ¦V¥k9Ä檺È
- ¤é´Á(Mystr) = Ar '§â°}¦C§@¬°¦r¨åª«¥ó¤º®e
- ElseIf IsError(Application.Match(A.Offset(, 9), ¤é´Á(Mystr), 0)) Then '¦pªGDÄæ¦V¥k9Ä檺Ȧb¦r¨åª«¥ó(¦¹³B·|¬O¤@Ó°}¦C)¤º§ä¤£¨ì
- Ar = ¤é´Á(Mystr) '¥ý¨ú¥X¦r¨åª«¥ó¤º®e¶Çµ¹°}¦CÅܼÆ
- s = UBound(Ar) + 1 '¬ö¿ý°}¦C¤WÈ(ÂX¥R°}¦C)
- ReDim Preserve Ar(s) '°}¦C·s¤¸¯À
- Ar(s) = A.Offset(, 9)
- ¤é´Á(Mystr) = Ar '±N¥HÂX¤jªº°}¦C¶Ç¦^¦r¨åª«¥ó
- Else '§_«h(¤é´Á¤w¸g¦s¦b)
- 'DÄæ¦V¥k9ÄæÅܦâ¡A¨Ã±N¿ù»~¤å¦r¦s¤J°}¦C
- A.Offset(, 9).Interior.ColorIndex = 36: ReDim Preserve ErrStr(x): ErrStr(x) = "«ô³X¤é´Á«½Æ": x = x + 1
- End If
- '§Ç¸¹°µªk»P¤é´Á¬Û¦P¡A¤£¦A²Öz
- If IsEmpty(§Ç¸¹(Mystr)) Then
- ReDim Preserve Ar(0)
- Ar(0) = A.Offset(, 12)
- §Ç¸¹(Mystr) = Ar
- ElseIf IsError(Application.Match(A.Offset(, 12), §Ç¸¹(Mystr), 0)) Then
- Ar = §Ç¸¹(Mystr)
- s = UBound(Ar) + 1
- ReDim Preserve Ar(s)
- Ar(s) = A.Offset(, 12)
- §Ç¸¹(Mystr) = Ar
- Else
- A.Offset(, 12).Interior.ColorIndex = 36: ReDim Preserve ErrStr(x): ErrStr(x) = "§Ç¸¹«½Æ": x = x + 1
- End If
- '¦pªG¿ù»~¦r¦ê°}¦C¤¸¯À¼Æ¶q¤£µ¥©ó0¡A´Nªí¥Ü¦³¿ù»~¡ADÄæ¦V¥k13ÄæÅܦâ¡A¨Ã¼g¤J¥H\³s±µªº¿ù»~¤å¦r¡A§_«h´N²M°£
- If x > 0 Then A.Offset(, 13).Interior.ColorIndex = 35: A.Offset(, 13) = Join(ErrStr, "\") Else A.Offset(, 13) = ""
- Erase ErrStr '¿ù»~¦r¦êªº°}¦C²MªÅ
- Next
- End With
- End Sub
½Æ»s¥N½X |
|