Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1140to1144
1140to1144
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Wert in Spalte weitersuchen (rückwärts)

Forumthread: Wert in Spalte weitersuchen (rückwärts)

Wert in Spalte weitersuchen (rückwärts)
Luca-Darius
Hallo an die VBA-Experten,
ich suche ein Makro was folgendes realisieren kann:
In Spalte D meiner Tabelle (Tabelle mit 32.000 Zeilen) sind nur wenige (oder gar keine) Zellen mit dem Wert 1 befüllt. In Spalte A steht in jeder Zelle ein Datum.
Ich möchte mit einer Input-Box die unterste Zelle in Spalte D mit dem Wert 1 finden und das dazugehörige Datum aus Spalte A soll dann in einer Message-Box ausgegeben werden. Bei jedem weiteren Klick auf den OK-Button der Input-Box soll die "Suche und Datumsausgabe" nach oben fortgeführt werden.
Wird keine Zahl 1 in Spalte D gefunden soll in einer Infobox "Kein zutreffendes Datum gefunden !" ausgeben werden und das Makro soll ohne weitere "Fehlermeldung" beendet werden.
Kann der OK-Button auch anders benannt werden? "OK" könnte am besten durch "Datum suchen" ersetzt werden. Ein zweiter Button mit "Abbrechen" soll das Makro jederzeit beenden/abbrechen können - das wäre wirklich perfekt!
Vielen Dank für eure Hilfe - Mfg Luca-Darius
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
02.03.2010 14:26:09
David
Hallo Luca,
warum nicht einfach per Autofilter?
Gruß
David
AW: Wert in Spalte weitersuchen (rückwärts)
02.03.2010 14:58:08
Uwe
Hallo, 

Sub aaa()
Dim rngC As Range, rngF As Range
On Error Resume Next
With Columns(4)
Set rngF = .Find(What:="1", after:=.Cells(1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Set rngC = rngF
If Not rngF Is Nothing Then
Do
MsgBox Cells(rngF.Row, 1)
Set rngF = .FindPrevious(rngF)
Loop Until rngC.Address = rngF.Address
Else
MsgBox "Kein zutreffendes Datum gefunden !"
End If
End With
End Sub
Der OK-Button lässt sich nicht umbenennen.
Gruß Uwe
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
02.03.2010 21:32:48
Luca-Darius
Hallo David,
Autofilter ist deshalb ausgeschieden, da nach einer einmaligen Filterung der Rechner aufgrund der Zeilenanzahl so belastet wird, dass eine Filterung nach einem weiteren Wert erst nach ca. 30 Sekunden möglich ist.
Hallo Uwe,
dein VBA-Code funktioniert gut. Es stellen sich jedoch noch zwei Probleme.
1) Es dauert ca. 15 Sekunden bis in der Spalte der erste Wert von unten gefunden wird. Könnte man in
dem VBA-Code den Zellbereich (E2 bis E31047) eingrenzen, um schneller zum ersten Wert (die
unterste Zahl 1 in Spalte E) zu gelangen. Sonst fängt er doch bestimmt in der Zeile 65.000 an zu
suchen, was natürlich dauert - oder ?
2) Wenn ich z.B. 300 Treffer habe möchte ich die Suche auch vorzeitig abbrechen können. Ein Klick auf das
Beenden-Kreuzchen in der Msg-Box funktioniert natürlich nicht. Was gibt es für Möglichkeiten ohne das
ganze Excel-Programm beenden/abschießen zu müssen ?
Danke - Luca-Darius
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
02.03.2010 22:10:00
Uwe
Hallo Luca-Darius,
die Find-Methode ist eigentlich die schnellste. Daran kann die Verzögerung nicht liegen.
Ich habe die Spalte mal komplett gefüllt und nur in Zeile 10 eine 1 eingetragen.
Die MsgBox kam ohne spürbare Verzögerung.
Deshalb habe ich zu Beginn mal eine Umschaltung auf manuelle Berechnung eingebaut.
Vielleicht hilft das. 

Sub ZeigeEinser()
Dim rngC As Range, rngF As Range
Dim iMsgBox As Integer, iCalc As Integer
iCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Resume Next
With Columns(4)
Set rngF = .Find(What:="1", after:=.Cells(1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Set rngC = rngF
If Not rngF Is Nothing Then
Do
iMsgBox = MsgBox(Cells(rngF.Row, 1), vbRetryCancel)
Set rngF = .FindPrevious(rngF)
Loop Until rngC.Address = rngF.Address Or iMsgBox = 2
Else
MsgBox "Kein zutreffendes Datum gefunden !"
End If
End With
Application.Calculation = iCalc
End Sub
Gruß Uwe
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
03.03.2010 00:47:59
Luca-Darius
Hallo Uwe,
Super - Problem 2 ist auf jeden Fall gelöst.
Bei Problem 1 habe ich festgestellt, wenn die "leeren Zellen" in Spalte D wirklich leer sind (gelöscht), dann funktioniert der Zugriff (wie in deinem Test) ohne Verzögerung. Meine "leeren Zellen" sind allerdings Formeln, die als Ergebnis eine leere Zelle ausgeben. Da in jeder Zelle ein Formel steht dauert die Find-Methode wirklich mehrere Sekunden. Selbst kopieren und dann "Einfügen Wert" macht die Prozedur nicht schneller, denn die nun leeren Zellen scheinen auch noch irgendetwas zu beinhalten, was das Ganze langsam macht. Kann man irgendwie die Formeln, die nur als Ergebnis "leeren Zellen" ausgeben bei der Suche ignorieren und so direkt zu der Zelle springen, die den Wert 1 ausgiebt ?
Mfg - Luca-Darius
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
03.03.2010 09:46:04
Uwe
Hallo Luca-Darius,
das vorherige Prüfen auf nicht NichtLeer würde den Code langsamer machen.
Aber probiere mal diese Variante: 

Sub ZeigeEinser_2()
Dim i As Long, vArray As Variant
vArray = Columns(4).Value
For i = UBound(vArray) To 1 Step -1
If vArray(i, 1) = 1 Then
If MsgBox(Cells(i, 1), vbRetryCancel) = vbCancel Then Exit For
End If
Next i
End Sub
Gruß Uwe
Anzeige
AW: Wert in Spalte weitersuchen (rückwärts)
03.03.2010 14:25:55
Luca-Darius
Hallo Uwe,
suuuuuuuuuuuper - das wars - ich bin begeistert !!!
Mfg - Luca-Darius
Anzeige
Anzeige

Infobox / Tutorial

Wert in Excel rückwärts suchen und Datum ausgeben

Schritt-für-Schritt-Anleitung

  1. Öffne Excel und lade die entsprechende Tabelle mit den Daten.
  2. Aktiviere die Entwicklertools: Gehe zu "Datei" > "Optionen" > "Menüband anpassen" und aktiviere die Entwicklertools.
  3. Füge ein neues Modul ein:
    • Klicke auf "Visual Basic" in den Entwicklertools.
    • Rechtsklicke auf "VBAProject (DeineDatei.xlsx)", wähle "Einfügen" und dann "Modul".

  4. Füge den VBA-Code ein: Kopiere den folgenden Code in das Modul:

    Sub ZeigeEinser()
   Dim rngC As Range, rngF As Range
   Dim iMsgBox As Integer, iCalc As Integer
   iCalc = Application.Calculation
   Application.Calculation = xlCalculationManual
   On Error Resume Next
   With Columns(4)
       Set rngF = .Find(What:="1", after:=.Cells(1), LookIn:=xlValues, LookAt:= _
       xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
       Set rngC = rngF
       If Not rngF Is Nothing Then
           Do
               iMsgBox = MsgBox(Cells(rngF.Row, 1), vbRetryCancel)
               Set rngF = .FindPrevious(rngF)
           Loop Until rngC.Address = rngF.Address Or iMsgBox = 2
       Else
           MsgBox "Kein zutreffendes Datum gefunden !"
       End If
   End With
   Application.Calculation = iCalc
End Sub
  5. Schließe den Visual Basic Editor und kehre zu Excel zurück.
  6. Führe das Makro aus: Gehe zu "Entwicklertools" > "Makros", wähle ZeigeEinser und klicke auf "Ausführen".

Häufige Fehler und Lösungen

  • Problem: Der Makro läuft zu langsam.

    • Lösung: Stelle sicher, dass die Spalte D nur die benötigten Werte enthält. Wenn leere Zellen Formeln enthalten, kann dies die Suche verlangsamen. Überlege, diese Formeln durch Werte zu ersetzen.

  • Problem: Es wird keine Zahl 1 gefunden.

    • Lösung: Überprüfe, ob die Zellen in Spalte D tatsächlich den Wert 1 beinhalten und keine Textformate oder Leerzeichen enthalten.

Alternative Methoden

Eine alternative Methode zum rückwärts suchen könnte die Verwendung des Autofilters sein, allerdings kann dies bei großen Datenmengen die Performance beeinträchtigen. Eine Möglichkeit, dies zu optimieren, ist die Verwendung der Datenbankfunktion DBS oder SVERWEIS, aber diese sind nicht so flexibel wie die VBA-Lösung.

Praktische Beispiele

  • Beispiel 1: Wenn deine Daten in Spalte D viele Formeln enthalten, die nur leere Zellen anzeigen, kannst du die Formel in den Zellen entfernen oder durch direkte Werte ersetzen, um die Suchgeschwindigkeit zu erhöhen.

  • Beispiel 2: Verwende den angepassten VBA-Code, um die Suche an einen bestimmten Bereich zu binden. Ändere die Zeile Set rngF = .Find(...) so, dass sie nur den gewünschten Zellbereich umfasst, z.B. Set rngF = .Find(What:="1", after:=.Cells(2, 4), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).

Tipps für Profis

  • Nutze manuelle Berechnung in großen Tabellen, um die Performance zu verbessern, wie im Code gezeigt.
  • Überlege, das Makro an die Bedürfnisse deines spezifischen Datensatzes anzupassen, z.B. durch Anpassungen in der Suchrichtung oder der Suchkriterien.
  • Verwende auch die Find-Methode als effiziente Suche, die in VBA implementiert ist.

FAQ: Häufige Fragen

1. Kann ich den OK-Button in der MsgBox umbenennen?
Nein, die Standard-MsgBox in VBA erlaubt keine Umbenennung der Schaltflächen.

2. Was mache ich, wenn das Makro nicht richtig funktioniert?
Überprüfe die Zellformate in Spalte D und stelle sicher, dass sie die korrekten Werte enthalten. Achte außerdem darauf, dass die Makros aktiviert sind.

3. Wie kann ich die Suche abbrechen?
Im Code ist bereits eine Option eingebaut, die es ermöglicht, die Suche mit dem "Abbrechen"-Button in der MsgBox zu beenden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige