- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-12 16:42 ½s¿è
¦^´_ 3# Andy2483
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
Option Explicit
Sub TEST_1()
Dim Arr, Brr, Crr, Z, i&, R&, X%, T$, T0$, T1$, T2$, T3$, xR As Range
Set Z = CreateObject("Scripting.Dictionary"): [E:E].ClearContents
Set xR = Range([B1], [A65536].End(3)): Brr = xR
With xR
For i = 48 To 57
.Replace Chr(i), " ", LookAt:=2: .Replace " ", " ", LookAt:=2
Next
Arr = xR: xR = Brr
With .Columns(2)
.Replace "-", "~", LookAt:=2
For i = 65 To 90: .Replace Chr(i), "", LookAt:=2: Next
Crr = xR: xR = Brr
End With
End With
ReDim Brr(1 To UBound(Brr) * 3, 1 To 1)
For i = 2 To UBound(Crr)
T = Crr(i, 1): T0 = T
T = Replace(T & "1|", StrReverse(Val(StrReverse(T & 1))) & "|", "")
If Z(T) = "" Then
Z(T) = R * 3 + 1: R = R + 1: Brr(Z(T), 1) = "PO# " & T0
T1 = Trim(Split(Arr(i, 2) & "-", "-")(0))
If T1 = "" Then: T1 = Split(Arr(i, 1), " ")(0)
If InStr(T1, " ") = 0 Then T1 = T1 & " "
Z(T & "|e") = T1: Z(T & "|n") = Crr(i, 2)
End If
If InStr(Brr(Z(T), 1), T0) = 0 Then Brr(Z(T), 1) = Brr(Z(T), 1) & "/" & T0
Z(T & "|n") = Z(T & "|n") & "," & Crr(i, 2)
T2 = Z(T & "|n"): T3 = N_L(T2, "~", ",")
If T3 = "" Then
Brr(Z(T) + 1, 1) = "#VALUE!"
Else
T2 = Replace(Z(T & "|e"), " ", "(" & N_L(T2, "~", ",") & ")")
Brr(Z(T) + 1, 1) = "C/NO. " & Replace(T2, "~", "-")
End If
Next
[E2].Resize(R * 3 - 1, 1) = Brr
Set Z = Nothing: Set xR = Nothing: Erase Arr, Brr, Crr
End Sub |
|