Ein Code der über alle geöffneten Mappen ( innerhalb einer Excelinstanz ) sucht und die Möglichkeit zum Weitersuchen bietet.

 

 

 

Public Sub Suchen()

'Code für ein allgemeines Modul

'********************************

'Autor: Jürgen Hennekes

'********************************

Dim WB As Workbook

Dim WBA As Workbook

Dim strInbox As String

Dim zelle As Range

Dim intWks As Integer

Dim Wahl As String

Dim FirstAddress

Dim bln As Boolean

 

Set WBA = ActiveWorkbook

 

strInbox = InputBox("Bitte einen Suchbegriff eingeben", "SUCHE")

If strInbox = "" Then

Exit Sub

End If

For Each WB In Workbooks

  WB.Activate

    For intWks = 1 To ActiveWorkbook.Sheets.Count

        With Worksheets(intWks).UsedRange

            Set zelle = .Find(strInbox, LookIn:=xlValues)

                If Not zelle Is Nothing Then

                    FirstAddress = zelle.Address

                    bln = True

                        Do

                            Worksheets(intWks).Select

                            zelle.Select

                            Wahl = _

                            MsgBox("[ " & strInbox & " ] gefunden in: " & vbCr _

                            & vbCr & "Mappe: " & WB.Name _

                            & vbCr & "Tabelle: " & Worksheets(intWks).Name _

                            & vbCr & " Zelle: " & zelle.Address(0, 0) _

                            & vbCr & vbCr & "Weitersuchen?", vbYesNo)

                            If Wahl = vbNo Then

                                Set zelle = Nothing

                                Exit Sub

                            End If

                          Set zelle = .FindNext(zelle)

                       Loop While Not zelle Is Nothing And zelle.Address <> FirstAddress

                End If

        End With

    Next intWks

Next WB

 

If bln = False Then

MsgBox "Der Suchbegriff [" & strInbox & "] wurde nicht gefunden."

End If

Set zelle = Nothing

WBA.Activate

End Sub