VBA Suche nach Leerstring mit range.find

Forumthread: VBA Suche nach Leerstring mit range.find

25.02.2020 10:21:31

Heribert

Moin allerseits
Vor das Problem gestellt, Strings in die erste freie Zelle eines Bereichs zu schreiben, habe ich frisch von der Leber weg einfach ein range.find ("") eingesetzt. Leider zeigte sich, dass die Methode nicht immer das erwartete Ergebnis liefert. Ich hab noch was rumgeprockelt und schließlich eine klassische/unelegante und vermutlich ineffiziente Lösung mit Abfragen in Schleifen verwendet, aber damit ist das Problem natürlich nicht vom Tisch.
Nachdem ich mich jetzt mal isoliert damit befasst habe, stehe ich eigentlich vor zwei Problemen:
1) Die Suche nach einem Leerstring in einer völlig leeren Tabelle liefert überhaupt kein Ergebnis (Nothing).
2) Die Suche nach dem ersten Leerstring in einem Bereich liefert ein falsches Ergebnis, wenn die erste Zelle bereits einmal einen Wert hatte und wieder auf einen Leerstring gesetzt wurde.
Zur Illustration ein -- hoffentlich selbsterklärendes -- Stückchen Code, das ein leeres Tabellenblatt öffnet, an diesem verschiedene simple Änderungen vornimmt und nach jeder dieser Änderungen wieder die find-Methode aufruft.

Option Explicit
Sub FindTest()
Dim x As Range
Application.ThisWorkbook.Worksheets.Add.Name = "FindTest"
' Ausgangssituation: Neues, absolut leeres Tabellenblatt
With Application.ThisWorkbook.Worksheets("FindTest")
' 1. Suche in leerem Tabellenblatt liefert kein Ergebnis
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
If x Is Nothing Then
Debug.Print "1. Kein Suchergebnis bei absolut leerem Tabellenblatt"
End If
Debug.Print
' 2. Suche in mit Leerstrings 'initialiertem' Tabellenblatt liefert kein Ergebnis
.Range("a1:x10").Value = ""
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
If x Is Nothing Then
Debug.Print "2. Kein Suchergebnis, nachdem A1:X10 mit Leerstrings gefüllt wurde"
End If
Debug.Print
' 3. Suche in mit beliebigem Wert in irgendeiner Zelle 'initialisiertem'
'    Tabellenblatt liefert das richtige Ergebnis
.Cells(1000, 1000).Value = " "
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
Debug.Print "3. Leerstring in " + x.Address + " gefunden, nachdem "
Debug.Print "   eine beliebige Zelle einen 'echten' Inhalt bekommen hat"
Debug.Print
' 4. Suche liefert das richtige Ergebnis, nachdem die Zelle A1 keinen
'    Leerstring mehr enthält
.Cells(1, 1).Value = "A"
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
Debug.Print "4. Leerstring in " + x.Address + " gefunden, nachdem "
Debug.Print "   ein Buchstabe in Zelle A1 geschrieben wurde"
Debug.Print
' 5. Suche liefert das falsche Ergebnis, nachdem die Zelle A1 wieder
'    einen Leerstring enthält
.Cells(1, 1).Value = ""
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
Debug.Print "5. Leerstring in " + x.Address + " gefunden, nachdem der "
Debug.Print "   Inhalt von Zelle A1 mit Leerstring überschrieben wurde"
Debug.Print
' 6. Suche liefert bei Wiederholung dasselbe falsche Ergebnis
Set x = .Range("A1:D4").Find("", LookIn:=xlValues, lookat:=xlWhole)
Debug.Print "6. Leerstring in " + x.Address + " gefunden, nachdem die Suche "
Debug.Print "   unverändert wiederholt wurde (d.h. kein implizites FindNext)"
Debug.Print
' 7. Suche liefert das richtige Ergebnis, wenn sie explizit
'    bei der letzten Zelle gestartet wird
Set x = .Range("A1:D4").Find("", after:=.Cells(4, 4), LookIn:=xlValues, lookat:=xlWhole)
Debug.Print "7. Leerstring in " + x.Address + " gefunden, nachdem die letzte Zelle des "
Debug.Print "   Bereichs als 'After' spezifiziert wurde"
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End Sub

Das Ganze ist unabhängig von SearchOrder, SearchDirection und SearchFormat, und das beschriebene Verhalten ist bei Excel 2010 und Excel 2016 zu beobachten:

1. Kein Suchergebnis bei absolut leerem Tabellenblatt
2. Kein Suchergebnis, nachdem A1:X10 mit Leerstrings gefüllt wurde
3. Leerstring in $A$1 gefunden, nachdem
eine beliebige Zelle einen 'echten' Inhalt bekommen hat
4. Leerstring in $A$2 gefunden, nachdem
ein Buchstabe in Zelle A1 geschrieben wurde
5. Leerstring in $A$2 gefunden, nachdem der
Inhalt von Zelle A1 mit Leerstring überschrieben wurde
6. Leerstring in $A$2 gefunden, nachdem die Suche
unverändert wiederholt wurde (d.h. kein implizites FindNext)
7. Leerstring in $A$1 gefunden, nachdem die letzte Zelle des
Bereichs als 'After' spezifiziert wurde

Hab ich irgendwas übersehen, mache ich irgendwas falsch?
Gruß
Heribert

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA Suche nach Leerstring mit range.find

28.02.2020 16:00:55

fcs
AW: VBA Suche nach Leerstring mit range.find

28.02.2020 16:24:59

Heribert

AW: VBA Suche nach Leerstring mit range.find

28.02.2020 16:00:55

fcs

Hallo Heribert,
Generell:

bei einem Zellbereich mit ausschließlich leeren Zellen liefert Excel bei der Suche immer als Ergebnis nothing - wo nichts ist, da findet Excel halt nichts. Hier muss man im Makro eine Prüfung einbauen, was dann passieren soll. Z.B. die erste Zelle des Bereichs als Ergebnis setzen.

beginnt Excel mit der Suche in der aktiven Zelle bzw. in der 1. Zelle des zu durchsuchenden Bereichs.

Unterscheidet Excel bei der Suche nicht zwischen leeren Zellen und Zellen mit Leerstring.

Um die 1. Zelle mit einem gesuchten Wert zu finden muss man die Suche in der letzten Zelle des Bereichs beginnen mit SearchDirection = xlNext, was der Standardwert ist.

Wenn alle Zellen befüllt sind, dann ist das Ergebnis ebenfalls Nothing. Dies muss man ggf. ebenfalls prüfen.

Ich habe jetzt mal ein Testmakro erstellt, das im aktiven Blatt sucht und die Adresse der gefundenen Zelle als Meldung ausgibt und Selektiert. Die Ermittlung der gesuchten Zelle erfolgt in einer Function, die dann auch in anderen VBA-Projekte einbauen kann.
LG
Franz

'Excelversion: Excel für Office 365
'Makro erstellt/bearbeitet am 2020-02-28
Sub Find_1st_Leerstring_Test()
Dim wks As Worksheet
Dim rngX As Range
Dim msgText As String, msgTitel As String, msgButton As Integer
msgTitel = "Test: Suche nach 1. Zelle mit Leerstring"
msgButton = vbInformation + vbOKOnly
Set wks = ActiveSheet
With wks
'zu durchsuchender Zellbereich
Set rngX = fncFind_1st(rngFind:=.Range("A1:D4"), varFind:="")
If rngX Is Nothing Then
msgText = "Alle Zellen im Bereich ""A1:D4"" sind ausgefüllt!"
MsgBox msgText, msgButton, msgTitel
Else
rngX.Select
msgText = "Leerstring gefunden in Zelle: " & rngX.Address(False, False, xlA1)
MsgBox msgText, msgButton, msgTitel
End If
End With
End Sub
Public Function fncFind_1st(rngFind As Range, varFind, _
Optional bolEmpty As Boolean = True) As Range
Dim x As Range, rngLast As Range
'rngFind = zu durchsuchender Zellbereich
'varFind = zu suchender Wert
'bolEmpty = wenn True dann wird, wenn Zellbereich nur leere Zellen enthält, _
die 1. Zelle des Bereich als Ergebnis zurückgegebn
With rngFind
'letzte Zelle
Set rngLast = rngFind.Cells(.Rows.Count, .Columns.Count)
Set x = rngFind.Find(varFind, after:=rngLast, LookIn:=xlValues, lookat:=xlWhole)
If x Is Nothing Then
If IsEmpty(rngFind.Range("A1")) And bolEmpty = True Then
Set x = rngFind.Range("A1")
End If
End If
End With
Set fncFind_1st = x
End Function

AW: VBA Suche nach Leerstring mit range.find

28.02.2020 16:24:59

Heribert

Hallo Franz,
vielen Dank für die Erklärung und den Workaround. Das erklärt zwar m. E. nicht den 3. Fall in meinem Beispiel, aber vmtl. werde ich damit leben können.
Wobei mir gerade eben erst auffällt, dass ich die deutsche Fassung der "offiziellen" Dokumentation die ganze Zeit falsch gelesen habe, also "Fall (sic!) Sie dieses Argument nicht angeben, beginnt die Suche nach der Zelle in der oberen linken Ecke des Bereichs." und nicht "... beginnt die Suche nach der Zelle in der oberen linken Ecke des Bereichs.", wie es gemeint (aber nicht wirklich logisch) ist. Das soll mich lehren ...
Beste Grüße
Heribert

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um in Excel VBA einen Leerstring mit der Methode Range.Find zu suchen, kannst du folgende Schritte befolgen:

Neues Arbeitsblatt erstellen: Erstelle ein neues Arbeitsblatt, um deine Tests durchzuführen.
```
Application.ThisWorkbook.Worksheets.Add.Name = "FindTest"
```
Suchbereich festlegen: Definiere den Bereich, in dem du nach dem Leerstring suchen möchtest.
```
Dim searchRange As Range
Set searchRange = Worksheets("FindTest").Range("A1:D4")
```

Leerstring suchen: Verwende die Find-Methode, um nach einem Leerstring zu suchen.

Dim foundCell As Range
Set foundCell = searchRange.Find("", LookIn:=xlValues, lookat:=xlWhole)

Ergebnisse auswerten: Überprüfe, ob ein Ergebnis gefunden wurde.

If foundCell Is Nothing Then
   Debug.Print "Kein Suchergebnis"
Else
   Debug.Print "Leerstring gefunden in: " & foundCell.Address
End If

Wiederhole die Suche: Um verschiedene Szenarien zu testen, kannst du Zellen im Bereich mit Werten und Leerstrings füllen und die Suche wiederholen.

Häufige Fehler und Lösungen

Kein Ergebnis bei leerem Tabellenblatt: Wenn der gesamte Bereich leer ist, gibt Range.Find Nothing zurück. Du solltest eine Prüfung einbauen, die das berücksichtigt und vielleicht die erste Zelle als Ergebnis zurückgibt.
Unterschied zwischen leeren Zellen und Leerstrings: Excel unterscheidet nicht zwischen einer leeren Zelle und einer Zelle, die einen Leerstring enthält. Das bedeutet, dass man nicht erwarten kann, dass Find bei Leerstrings immer ein Ergebnis zurückgibt.
Suchrichtung beachten: Wenn du die erste Zelle mit einem gesuchten Wert finden möchtest, sollte die Suche in der letzten Zelle des Bereichs beginnen mit SearchDirection = xlNext.

Alternative Methoden

Wenn die Range.Find-Methode nicht die gewünschten Ergebnisse liefert, kannst du auch folgende Alternativen in Betracht ziehen:

Schleifen verwenden: Iteriere durch die Zellen des Bereichs und prüfe jede Zelle auf einen Leerstring.

Dim cell As Range
For Each cell In searchRange
   If cell.Value = "" Then
       Debug.Print "Leerstring gefunden in: " & cell.Address
       Exit For
   End If
Next cell

Verwendung von WorksheetFunction.CountBlank: Diese Funktion zählt die leeren Zellen in einem Bereich, was hilfreich sein kann, um herauszufinden, ob Leerstrings vorhanden sind.

Praktische Beispiele

Hier sind einige Beispiele zur Verwendung von Range.Find in Kombination mit verschiedenen Szenarien:

Suche nach Leerstring nach dem Füllen von Zellen:

Worksheets("FindTest").Range("A1:D4").Value = ""
Set foundCell = searchRange.Find("", LookIn:=xlValues, lookat:=xlWhole)

Erster nicht-leerer Wert:

Worksheets("FindTest").Cells(1, 1).Value = "Test"
Set foundCell = searchRange.Find("", after:=searchRange.Cells(searchRange.Cells.Count), LookIn:=xlValues, lookat:=xlWhole)

Tipps für Profis

Effiziente Suche: Achte darauf, die Suche in der letzten Zelle des Bereichs zu starten, um die Leistung zu optimieren, besonders bei großen Datenmengen.
Fehlerbehandlung: Implementiere eine umfassende Fehlerbehandlung, um sicherzustellen, dass dein Makro nicht abstürzt, wenn es auf unerwartete Daten stößt.
Dokumentation lesen: Lies die offizielle Excel-Dokumentation zu Range.Find, um die verschiedenen Argumente und deren Auswirkungen besser zu verstehen.

FAQ: Häufige Fragen

1. Warum finde ich keinen Leerstring in einem leeren Bereich?
Wenn der Bereich vollständig leer ist, gibt die Find-Methode Nothing zurück, da Excel nichts findet.

2. Wie kann ich einen Leerstring in einer Zelle suchen, die zuvor einen Wert hatte?
Um sicherzustellen, dass du den Leerstring findest, starte die Suche in der letzten Zelle des Bereichs und achte darauf, dass du den Suchparameter korrekt setzt.

3. Gibt es eine Möglichkeit, die Suche zu optimieren?
Ja, indem du die Find-Methode mit der richtigen Startzelle und Suchrichtung verwendest, kannst du die Effizienz deiner Suche erhöhen.

Beliebteste Forumthreads (12 Monate)

VBA Suche nach Leerstring mit range.find

Forumthread: VBA Suche nach Leerstring mit range.find

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

VBA Suche nach Leerstring mit Range.Find

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread