VBA suchen in allen Tabellen

Forumthread: VBA suchen in allen Tabellen

Lutz

Hallo Excel-Profis,
ich habe mir einen Code ergoogelt der in allen Tabellen sucht, klappt auch wunderbar.
Mein Problem: er soll nicht die Zielfundstelle anwählen sondern 2 Zeilen weiter oben und eine Spalte nach links.
Was muß man da ändern?

Sub Suchen_alle_Tabellen()
Dim wks As Worksheet
Dim rng As Range
Dim strAddress As String, strFind As String
strFind = InputBox("Bitte Suchbegriff eingeben:", Application.UserName, strSuch)
If strFind = "" Then Exit Sub
For Each wks In Worksheets
Set rng = wks.Cells.Find(strFind, lookat:=xlPart, LookIn:=xlValues)
If Not rng Is Nothing Then
strAddress = rng.Address
Do
Application.Goto rng, False
If MsgBox("Weiter", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Set rng = Cells.FindNext(After:=ActiveCell)
If rng.Address = strAddress Then Exit Do
Loop
End If
Next wks
strSuch = strFind
MsgBox "Keine weiteren Fundstellen!", False, Application.UserName
Worksheets(1).Activate
Range("A1").Select
End Sub

Vielen Dank für Eure Hilfe
Gruß Lutz

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Offset vom Suchergebnis

Erich
AW: Offset vom Suchergebnis

Lutz

Offset vom Suchergebnis

23.04.2010 18:42:18

Erich

Hi,
probier mal


Option Explicit
Sub Suchen_alle_Tabellen()
Dim wks As Worksheet
Dim rng As Range
Dim strAddress As String, strFind As String
Dim strSuch As String
strSuch = "abc"
strFind = InputBox("Bitte Suchbegriff eingeben:", Application.UserName, strSuch)
If strFind = "" Then Exit Sub
For Each wks In Worksheets
Set rng = wks.Cells.Find(strFind, lookat:=xlPart, LookIn:=xlValues)
If Not rng Is Nothing Then
strAddress = rng.Address
Do
Application.Goto rng.Offset(-2, -1), False
If MsgBox("Weiter", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Set rng = Cells.FindNext(After:=rng)
If rng.Address = strAddress Then Exit Do
Loop
End If
Next wks
strSuch = strFind
MsgBox "Keine weiteren Fundstellen!", False, Application.UserName
Worksheets(1).Activate
Range("A1").Select
End Sub

strSuch habe ich ergänzt, da sonst nicht kompilierbar.
Es wird nicht geprüft, dass rng.Offset(-2, -1) existiert, also rng nicht in Zeile 1 oder 2 und nicht in Spalte A ist.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Offset vom Suchergebnis

23.04.2010 20:08:59

Lutz

Hallo Erich,
vielen Dank, geht wie verrückt. Ich war jetzt in der Zwischenzeit auf:

Sub Suchen_alle_Tabellen()
Dim wks As Worksheet
Dim rng As Range
Dim strAddress As String, strFind As String
strFind = InputBox("Bitte Suchbegriff eingeben:", Application.UserName, strSuch)
If strFind = "" Then Exit Sub
For Each wks In Worksheets
Set rng = wks.Cells.Find(strFind, lookat:=xlPart, LookIn:=xlValues)
If Not rng Is Nothing Then
strAddress = rng.Address
Do
Application.Goto rng, True
ActiveWindow.SmallScroll ToLeft:=1
'ActiveCell.Offset(-2, -1).Select
If MsgBox("Weiter", vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then Exit  _
Sub
Set rng = Cells.FindNext(After:=ActiveCell)
If rng.Address = strAddress Then Exit Do
Loop
End If
Next wks
strSuch = strFind
MsgBox "Keine weiteren Fundstellen!", False, Application.UserName
Worksheets(1).Activate
Range("A1").Select
End Sub

gekommen.
Habe noch vorgegeben, das "nein" vorgegeben wird. Ich suche ein Datum und das gibt es eigentlich immer nur einmal.
Vielen Dank und ein schönes Wochenende
Gruß Lutz

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um in Excel VBA in allen Tabellenblättern nach einem bestimmten Wert zu suchen, kannst du den folgenden VBA-Code verwenden. Dieser Code ermöglicht es dir, nicht nur den Suchbegriff einzugeben, sondern auch das Ergebnis um zwei Zeilen nach oben und eine Spalte nach links zu verschieben.

Sub Suchen_alle_Tabellen()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strAddress As String, strFind As String
    strFind = InputBox("Bitte Suchbegriff eingeben:", Application.UserName)
    If strFind = "" Then Exit Sub

    For Each wks In Worksheets
        Set rng = wks.Cells.Find(strFind, lookat:=xlPart, LookIn:=xlValues)
        If Not rng Is Nothing Then
            strAddress = rng.Address
            Do
                Application.Goto rng.Offset(-2, -1), False
                If MsgBox("Weiter", vbYesNo + vbQuestion) = vbNo Then Exit Sub
                Set rng = Cells.FindNext(After:=rng)
                If rng.Address = strAddress Then Exit Do
            Loop
        End If
    Next wks

    MsgBox "Keine weiteren Fundstellen!", False, Application.UserName
    Worksheets(1).Activate
    Range("A1").Select
End Sub

Dieser Code verwendet die Find-Methode, um den Suchbegriff in allen Tabellenblättern zu suchen. Die Verwendung von Offset ermöglicht es dir, das Ergebnis entsprechend zu verschieben.

Häufige Fehler und Lösungen

Fehler: "Objektvariable nicht gesetzt"
- Lösung: Überprüfe, ob der Suchbegriff tatsächlich in den Tabellenblättern vorhanden ist. Falls nicht, wird rng nicht zugewiesen.
Fehler: Suchen in der ersten Zeile oder Spalte
- Lösung: Füge eine Überprüfung hinzu, um sicherzustellen, dass das Offset nicht über die Grenzen der Tabelle hinausgeht:
```
If rng.Row > 2 And rng.Column > 1 Then
   Application.Goto rng.Offset(-2, -1), False
Else
   MsgBox "Suchergebnis außerhalb des Blattes!", vbExclamation
End If
```

Alternative Methoden

Eine alternative Methode, um Werte in mehreren Tabellenblättern zu suchen, ist die Verwendung von Excel-Formeln wie SVERWEIS oder INDEX-VERGLEICH, jedoch sind diese weniger flexibel als ein VBA-Makro. Wenn du oft nach Werten suchst, kann ein Excel Makro suchen und die Ergebnisse auf einer neuen Zusammenfassungsseite darstellen, die effizientere Lösung sein.

Praktische Beispiele

Beispiel 1: Suche nach einem Namen in allen Tabellenblättern und zeige die Position des Ergebnisses an.
Beispiel 2: Suche nach einem bestimmten Datum in einem Arbeitsblatt und navigiere zur Position, indem du den Offset anwendest.

Diese Beispiele sind besonders nützlich, wenn du in großen Arbeitsmappen mit vielen Tabellenblättern arbeitest und schnell auf relevante Informationen zugreifen möchtest.

Tipps für Profis

Verwende Option Explicit am Anfang deines Codes, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
Füge Fehlerbehandlungen hinzu, um unerwartete Probleme während der Ausführung zu lösen.
Experimentiere mit der Find-Methode, um verschiedene Suchparameter zu verwenden (z.B. lookat:=xlWhole für exakte Übereinstimmungen).

FAQ: Häufige Fragen

1. Wie kann ich den Code anpassen, um nur in bestimmten Tabellenblättern zu suchen?
Du kannst eine Bedingung hinzufügen, die überprüft, ob das aktuelle Tabellenblatt in einer Liste von gewünschten Blättern enthalten ist.

2. Was mache ich, wenn mein Suchbegriff nicht gefunden wird?
Der Code gibt eine Nachricht aus, wenn keine weiteren Fundstellen gefunden werden. Du kannst diese Nachricht anpassen oder zusätzliche Logik hinzufügen, um alternative Aktionen durchzuführen.

Beliebteste Forumthreads (12 Monate)