- ©«¤l
- 2827
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2883
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-10-27
|
Sub TEST_V1()
Dim Arr, A, xD, i&, j%, N&, T$, V%
[¦¨ªG!A2:D6000].ClearContents
Set xD = CreateObject("scripting.dictionary")
Arr = Range([¥Ø¼Ð!C1], [¥Ø¼Ð!A65536].End(xlUp))
For i = 2 To UBound(Arr)
T = Trim(Arr(i, 1)): If T <> "" Then xD(T) = 1
T = ©î¸Ñ½s¸¹(Trim(Arr(i, 3))): If T = "" Then GoTo 101
For Each A In Split(T, "/"): xD(A & "") = 1: Next
101: Next i
Arr = Range([®w¦s!D1], [®w¦s!A65536].End(xlUp))
For i = 2 To UBound(Arr)
If xD("|" & i) > 0 Then GoTo 102 '¦pªG¸Ó¦æ¤w³Q´£¨ú¹L, ²¤¹L, ÁקK«Âд£¨ú
T = Trim(Arr(i, 1)): If Val(xD(T)) > 0 Then V = 1: GoTo 999 '[«~¸¹]¬Û²Å§Yª½±µ´£¨ú
T = Trim(Arr(i, 3)): If T = "" Then GoTo 102
T = ©î¸Ñ½s¸¹(T) '©î¸Ñ[³W®æ]
For Each A In Split(T, "/")
If A <> "" And Val(xD(A & "")) > 0 Then V = 1: Exit For
Next
999:
If V = 0 Then GoTo 102
N = N + 1: V = 0
For j = 1 To 4: Arr(N, j) = Trim(Arr(i, j)): Next
xD("|" & i) = 1 '¤w´£¨ú¦æ¸¹¦ì¸m,°O¿ý¤J¦r¨å
102: Next i
If N > 0 Then [¦¨ªG!A2:D2].Resize(N) = Arr
End Sub
'==========================================
Function ©î¸Ñ½s¸¹(xS$) As String
Dim TT$, j%, ST$
If xS = "" Then Exit Function
If Left(xS, 4) Like "####" Then TT = Left(xS, 4)
If Left(xS, 5) Like "####[A-Z]" Then TT = Left(xS, 5) & "/" & TT
If Left(xS, 5) Like "[A-Z]####" Then TT = Left(xS, 5) & "/" & TT
If Left(xS, 8) Like "???-????" Then TT = Left(xS, 8) & "/" & TT
xS = xS & "-"
For j = Len(TT) + 2 To Len(xS)
If Mid(xS, j, 1) Like "[-.(]" Then TT = Left(xS, j - 1) & "/" & TT
Next j
©î¸Ñ½s¸¹ = TT
End Function
¼Ò½k¤¤¤S¤£¯à¶Ã§ì, Ãø~~~ |
|