Question

I have a workbook where there are 5 sheets and 500 rows each. I want a macro which can search for the string which may present multiple time in the workbook. Please help me with this.

Regards, MDS

Answer 1

当你发现有str时,你想要做什么? 非macro方法是Edit- Find,并点击了选项纽扣。 • 将“带进”工作手册,点击Find All。这将给你一份名单,列出发现的所有座标。

如果你真的想要一个宏观的话,那就将把这些地点印在眼前的窗口上。

Sub FindAll()

    Dim sh As Worksheet
    Dim rFound As Range
    Dim sFirst As String
    Dim sWhat as String

    sWhat = Application.InputBox("Search for:")

    For Each sh In ThisWorkbook.Worksheets
        Set rFound = sh.UsedRange.Find(sWhat, , xlValues, xlPart)
        If Not rFound Is Nothing Then
            sFirst = rFound.Address
            Debug.Print rFound.Address(, , , True)
            Do
                Set rFound = sh.UsedRange.FindNext(rFound)
                If rFound Is Nothing Then Exit Do
                Debug.Print rFound.Address(, , , True)
            Loop Until rFound.Address = sFirst
        End If
    Next sh

End Sub

友情链接