Option Explicit
Sub TEST()
Dim Fn, Rg, Brr, Crr, i&, j&, Sh
Set Sh = ActiveSheet
Brr = Range(Sh.[A1], Sh.[A65536].End(3))
Set Rg = CreateObject("VBScript.RegExp")
Rg.Pattern = "\d+"
Rg.Global = True
ReDim Crr(1 To UBound(Brr), 0) As String
For i = 1 To UBound(Brr)
Set Fn = Rg.Execute(Brr(i, 1))
If Fn.Count > 0 Then
For j = 0 To Fn.Count - 1 '請教各位前輩,可以用Join取代這迴圈嗎?
Crr(i, 0) = Crr(i, 0) & Fn(j)
Next
End If
Next
Sh.[D1].Resize(UBound(Brr), 1) = Crr
End Sub
'以下是測試一維陣列Join
Sub 一維陣列Join()
Dim T
T = [{1,2,3,4,5,6}]
MsgBox T(1)
MsgBox Join(T)
End Sub
Option Explicit
Sub TEST() '今天學習到 以正則結果置換
Dim i&, Brr, Sh
'↑宣告變數:i是長整數變數,其他是通用型變數
Set Sh = ActiveSheet
'↑令Sh這通用型變數是現用工作表
Brr = Range(Sh.[A1], Sh.[A65536].End(3))
'↑令Brr是 二維陣列!以[A1]到A欄有內容最後一個儲存格,這範圍儲存格值倒入
With CreateObject("VBSCRIPT.REGEXP")
'↑以下是有關於正則的程序
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到Brr陣列縱向最大索引列號數
.Global = True
'↑設定全局可用
.Pattern = "\D+"
'↑匹配模式為非數字
Brr(i, 1) = "'" & .Replace(Brr(i, 1), "")
'↑令i迴圈列第1欄Brr陣列值是 "'"單引號連接 Brr陣列值至換掉非數字的正則
Next
End With
Sh.[E1].Resize(UBound(Brr), 1) = Brr
'↑令[E1]向下擴展 Brr陣列縱向最大索引列號數列,向右不擴展的範圍以Brr陣列值倒入
End Sub
發表於 2022-12-26 15:47
| 
