Re: [VBA ]這種字串切割的邏輯要怎麼寫

看板Office作者zchien (小建)時間12年前 (2013/07/24 19:29)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串3/3 (看更多)

※ 引述《windknife18 (windknife18)》之銘言： : 簡易版請參考 : Option Explicit : Option Base 1 : Sub test() : Dim Rng As Range : Dim str() As String : [H:I].ClearContents : For Each Rng In Range([A1], Cells(Rows.Count, 1).End(3)) : str = Split(WorksheetFunction.Trim(Rng(, 1)), " ") : Rng(, 8) = "'" & str(5) & "-" & str(6) : Rng(, 9) = str(8) : Next : End Sub 感謝W網友的幫忙...上面功能真是精簡...而且可以跑出我要的結果.. 另外我有看到其他網友建議..可以用split去擷取空白,在過濾空白也可以我嘗試寫了一個程式...也提供大家參考... Dim myArray() As String Dim myArray2() As String Dim i As Integer, j As Integer [H:I].ClearContents k = 1 For j = 1 To 10 myArray = Split(Cells(j, 1), " ") ReDim myArray2(UBound(myArray)) As String For i = 0 To UBound(myArray) If myArray(i) <> "" Then myArray2(k) = myArray(i) k = k + 1 End If Cells(j, 8).Value = "'" & myArray2(6) & "-" & myArray2(7) Cells(j, 9).Value = myArray2(9) Next i k = 1 Next j ====== 另外附上soyoso 網友的另外一個程式碼..看起來也很厲害只是我程度不夠...看不太懂... Sub test() Dim Rng As Range, Arr(9), CharStart&, CharEnd&, cnt&, str$ [H:I].ClearContents For Each Rng In Range([A1], Cells(Rows.Count, 1).End(3)) str = " " & Rng & " ": CharEnd = 1: cnt = 0 Do Back: CharStart = Application.Search(" ", str, CharEnd) CharEnd = Application.Search(" ", str, CharStart + 1) If CharEnd - CharStart = 1 Then GoTo Back cnt = cnt + 1: Arr(cnt) = Trim(Mid(str, CharStart, CharEnd - CharStart)) Loop Until CharEnd >= Len(str) Rng(, 8) = "'" & Arr(6) & "-" & Arr(7) Rng(, 9) = Arr(9) Erase Arr Next End Sub -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 123.205.138.242