2073Excel尋找字串,並複製資料至新位置-VBA

Excel尋找字串,並複製資料至新位置-VBA

2073

Excel尋找字串,並複製資料至新位置-VBA

實用性

●○○

難易度

●○○○

範本檔

2073.XLSX

錦子老師,請問如何尋找A欄裡有""的字串,並複製該欄位至資料表內的新位置?感謝幫忙!

(下圖是簡單製作的範例,欄位裡的內容很長1700筆,請見諒!)

 

其實這個問題也可以用VBA來解決。

Sub find_cha()

        Dim myRng As Range, allRng As Range, i As Long

        Dim firstAddress As String

        Set allRng = Application.Intersect(Range("A:A"), Cells.Parent.UsedRange)

        Set myRng = allRng.Find(what:="", LookAt:=xlPart)

        If myRng Is Nothing Then Exit Sub

        firstAddress = myRng.Address

        i = 0

        Do

                i = i + 1

                Cells(i, "C") = myRng.Value

                Set myRng = allRng.FindNext(myRng)

        Loop Until myRng.Address = firstAddress

End Sub