Excel VBA를 사용하여 워크북에서 모든 일치 항목 찾기

Excel VBA를 사용하여 워크북에서 모든 일치 항목 찾기

나는 글을 쓰려고 노력하고 있습니다.VBA문자열을 사용하여 주어진 Excel 워크북을 검색하고 가능한 모든 일치 항목을 나에게 반환하는 루틴입니다.

나는 현재 작동하는 구현체를 가지고 있지만, 그것은 루프를 위한 이중이기 때문에 매우 느립니다.물론 기본 제공되는 ExcelFind함수는 단일 일치 항목을 찾기 위해 "최적화"되지만, 추가 방법을 적용할 수 있는 초기 일치 항목 배열을 반환하고 싶습니다.

내가 이미 가지고 있는 것의 유사 코드를 게시할 것입니다.

For all sheets in workbook
    For all used rows in worksheet
        If cell matches search string
            do some stuff
        end
    end
end

앞서 언급했듯이, 이 이중 루프는 일을 매우 느리게 진행하기 때문에 가능하다면 이것을 제거하려고 합니다.좋은 의견이라도 있나?

갱신하다

아래 답변들은 제 방법을 개선했을 것이지만, 저는 여러 가지 질문을 반복적으로 해야 하기 때문에 약간 다른 것을 하게 되었습니다.

대신 문서의 모든 행을 반복하고 각 고유 행에 대한 키를 포함하는 사전을 만들기로 결정했습니다.이 값이 가리키는 값은 가능한 일치 항목의 목록이 되므로 나중에 쿼리할 때 해당 항목이 있는지 확인하고, 있는 경우 빠른 일치 항목 목록을 얻을 수 있습니다.

기본적으로 관리 가능한 구조에 모든 것을 저장하기 위해 초기 스위프를 한 번만 수행하고 다음에 수행할 수 있는 구조를 쿼리합니다.O(1)시간을

범위 사용.위에서 지적한 것처럼 워크북의 각 워크시트에 대한 루프와 함께 방법 찾기가 가장 빠른 방법입니다.예를 들어 다음은 각 워크시트에서 "Question?" 문자열을 찾아 "Answered!" 문자열로 대체합니다.

Sub FindAndExecute()

Dim Sh As Worksheet
Dim Loc As Range

For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
        Set Loc = .Cells.Find(What:="Question?")
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                Loc.Value = "Answered!"
                Set Loc = .FindNext(Loc)
            Loop
        End If
    End With
    Set Loc = Nothing
Next

End Sub

Ahmed의 답변을 바탕으로 다른 "찾기" 매개 변수를 포함하여 정리 및 일반화한 후 이 기능을 어떤 상황에서도 사용할 수 있습니다.

'Uses Range.Find to get a range of all find results within a worksheet
' Same as Find All from search dialog box
'
Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range
    Dim SearchResult As Range
    Dim firstMatch As String
    With rng
        Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
        If Not SearchResult Is Nothing Then
            firstMatch = SearchResult.Address
            Do
                If FindAll Is Nothing Then
                    Set FindAll = SearchResult
                Else
                    Set FindAll = Union(FindAll, SearchResult)
                End If
                Set SearchResult = .FindNext(SearchResult)
            Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch
        End If
    End With
End Function

사용법은 기본값과 동일합니다.다음은 요청에 따라 사용 예를 찾을 수 있습니다.

Sub test()
  Dim SearchRange As Range, SearchResults As Range, rng As Range
    Set SearchRange = MyWorksheet.UsedRange
    Set SearchResults = FindAll(SearchRange, "Search this")
    
    If SearchResults Is Nothing Then
        'No match found
    Else
        For Each rng In SearchResults
            'Loop for each match
        Next
    End If
End Sub

Function GetSearchArray(strSearch)
Dim strResults As String
Dim SHT As Worksheet
Dim rFND As Range
Dim sFirstAddress
For Each SHT In ThisWorkbook.Worksheets
    Set rFND = Nothing
    With SHT.UsedRange
        Set rFND = .Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rFND Is Nothing Then
            sFirstAddress = rFND.Address
            Do
                If strResults = vbNullString Then
                    strResults = "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"
                Else
                    strResults = strResults & "|" & "Worksheet(" & SHT.Index & ").Range(" & Chr(34) & rFND.Address & Chr(34) & ")"
                End If
                Set rFND = .FindNext(rFND)
            Loop While Not rFND Is Nothing And rFND.Address <> sFirstAddress
        End If
    End With
Next
If strResults = vbNullString Then
    GetSearchArray = Null
ElseIf InStr(1, strResults, "|", 1) = 0 Then
    GetSearchArray = Array(strResults)
Else
    GetSearchArray = Split(strResults, "|")
End If
End Function

Sub test2()
For Each X In GetSearchArray("1")
    Debug.Print X
Next
End Sub

찾기 루프를 수행할 때 무한 루프에 빠지지 않도록 주의하십시오.처음 발견된 셀 주소를 참조하고 각 "다음 찾기" 문 이후를 비교하여 처음 발견된 셀로 되돌아가지 않았는지 확인합니다.

범위를 사용할 수 있습니다.방법 찾기:

http://msdn.microsoft.com/en-us/library/office/ff839746.aspx

검색 문자열이 포함된 첫 번째 셀이 표시됩니다.다음 셀로 "이후" 인수를 설정하고 이 작업을 반복하면 첫 번째 항목으로 돌아갈 때까지 다른 모든 항목이 표시됩니다.

이것이 훨씬 더 빠를 것 같습니다.

B Hart의 답에 기초하여, 범위에서 값을 검색하고 발견된 모든 범위(셀)를 반환하는 함수의 내 버전은 다음과 같습니다.

Function FindAll(ByVal rng As Range, ByVal searchTxt As String) As Range
    Dim foundCell As Range
    Dim firstAddress
    Dim rResult As Range
    With rng
        Set foundCell = .Find(What:=searchTxt, _
                              After:=.Cells(.Cells.Count), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
        If Not foundCell Is Nothing Then
            firstAddress = foundCell.Address
            Do
                If rResult Is Nothing Then
                    Set rResult = foundCell
                Else
                    Set rResult = Union(rResult, foundCell)
                End If
                Set foundCell = .FindNext(foundCell)
            Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress
        End If
    End With

    Set FindAll = rResult
End Function

전체 워크북에서 값 검색하기

Dim wSh As Worksheet
Dim foundCells As Range
For Each wSh In ThisWorkbook.Worksheets
    Set foundCells = FindAll(wSh.UsedRange, "YourSearchString")
    If Not foundCells Is Nothing Then
        Debug.Print ("Results in sheet '" & wSh.Name & "':")
        Dim cell As Range
        For Each cell In foundCells
            Debug.Print ("The value has been found in cell: " & cell.Address)
        Next
    End If
Next

데이터를 배열로 읽을 수 있습니다.여기서 한 번에 하나의 셀을 읽는 대신 메모리에서 일치를 수행할 수 있습니다.

셀 내용을 VBA 배열로 전달

아래 코드는 무한 루프 생성을 방지합니다.XYZ가 워크북에서 찾고 있는 문자열이라고 가정합니다.

   Private Sub CommandButton1_Click()
   Dim Sh As Worksheet, myCounter
   Dim Loc As Range

   For Each Sh In ThisWorkbook.Worksheets
   With Sh.UsedRange
   Set Loc = .Cells.Find(What:="XYZ")

    If Not Loc Is Nothing Then

           MsgBox ("Value is found  in " & Sh.Name)
           myCounter = 1
            Set Loc = .FindNext(Loc)

    End If
End With
Next
If myCounter = 0 Then
MsgBox ("Value not present in this worrkbook")
End If

End Sub

언급URL : https://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba