錦子老師

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