Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

FindNext und verbundene Zellen | Herbers Excel-Forum


Betrifft: FindNext und verbundene Zellen von: Reinhard
Geschrieben am: 03.02.2012 01:07:06

Hallo Wissende,

in dieser Mappe:
https://www.herber.de/bbs/user/78706.xls
steht der Wert 42 in Tabelle1!E7, sie ist verbunden mit E8.
Die erste Prozedur mit entsprechendem Namen bringt den Fehler 91 in dieser Codezeile:
Loop While Not C Is Nothing And C.Address <> firstaddress

Deshalb habe ich Prozedur 2 gebastelt, die läuft fehlerfrei auch wenn die Zellen weiter verbunden bleiben.

Frage1:
Seltsam finde ich es, wenn ich der gezeigten Mappe die Zellverbindung aufhebe so ist der Fehler im Code weg. Warum kommt da eigentlich der Fehler in der ersten Prozedur wenn sie noch verbunden sind?

Frage2:
Ich habe viel getestet und kann irgendwie das Problem nicht nachstellen, siehe diese Mappe:
https://www.herber.de/bbs/user/78707.xls
Dort ist E7 auch verbunden aber das gleiche Makro Fehler_91 läuft da gut durch.
Ergo liegt da der Fehler der ersten Mappe nicht an den verbundenen Zellen sondern an etwas anderem obwohl der Fehler weg ist wenn man die Zellverbindung aufhebt.

Mir ist das alles ein Rätsel *kopfkratz*

Danke ^ Gruß
Reinhard

Option Explicit

Sub Fehler_91()
Dim C As Range, firstaddress As String, Zei As Long, wks2 As Worksheet
Set wks2 = Worksheets("Tabelle2")
With Worksheets("Tabelle1")
  Set C = .UsedRange.Find(42, , xlValues)
  If Not C Is Nothing Then
    firstaddress = C.Address
    Do
      Zei = Zei + 1
      wks2.Cells(Zei + 1, 1) = C.Address
      Set C = .UsedRange.FindNext(C)
    Loop While Not C Is Nothing And C.Address <> firstaddress
  End If
End With
End Sub

Sub Funktioniert()
Dim C As Range, firstaddress As String, Zei As Long, wks2 As Worksheet
Set wks2 = Worksheets("Tabelle2")
With Worksheets("Tabelle1")
  Set C = .UsedRange.Find(42, , xlValues)
  If Not C Is Nothing Then
    firstaddress = C.Address
    Do
      Zei = Zei + 1
      wks2.Cells(Zei + 1, 1) = C.Address
      Set C = .UsedRange.FindNext(C)
      If C Is Nothing Then Exit Do
    Loop While C.Address <> firstaddress
  End If
End With
End Sub

  

Betrifft: zu Frage1 von: Erich G.
Geschrieben am: 03.02.2012 01:34:10

Hi Reinhard,
hast du in der Mappe zu Frage 1 mal ganz ohne VBA das Suchen und weitersuchen per Hand ausprobiert,
mit verbundenen und getrennten Zellen?

Wenn E7:E8 verbunden sind, wird E7 gefunden und, da verbunden, mit E8 zusammen markiert.
Beim Weitersuchen meint Excel dann - da mehr als 1 Zelle markiert ist - es soll nur im markierten Bereich weitergesucht werden.
Resultat: Keine weitere Fundstelle. (VBA: Nothing, C.Address gips nich - Fehler)

Wenn E7:E8 nicht verbunden sind, wird E7 gefunden und allein markiert.
Beim Weitersuchen meint Excel dann - da nur 1 Zelle markiert ist - es soll auf dem ganzen Blatt weitergesucht werden.
Resultat: E7 wird wieder gefunden. (VBA: Not Nothing, =firstaddress)

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: zu Frage1 von: Reinhard
Geschrieben am: 03.02.2012 01:57:36

Hallo Erich,

nein, ich hab mir zwar verschieden Testmappen gebaut aber da nur mit Vba getestet.
Ich habe also nicht mit Excel gesucht.
Alles was du da sagt klingt schlüssig, logisch und ich verstehe das auch.

ABER in der zweiten Mappe ist der gleiche Code wie in der ersten.
In der ersten Mappe funktioniert er NICHT bei verbundenen Zellen.
In der zweiten Mappe funktioniert er ohne Fehler auch bei verbundenen Zellen.

Danke
Reinhard


  

Betrifft: Klingt zwar logisch, Erich, aber gib zu, den ... von: Luc:-?
Geschrieben am: 04.02.2012 05:47:52

…manuellen Teil hast du frei erfunden, denn Xl verhält sich nicht so! ;-)
Xl9 findet so alles außer versteckte Werte → in ausgeblendeten Zeilen/Spalten und VerbundZellen. Mit Fkt VERGLEICH in 1er Fml wdn auch die gefunden, aber natürl immer nur der 1.Wert.
Gruß+schöWE, Luc :-?


  

Betrifft: @Luc von: Erich G.
Geschrieben am: 04.02.2012 07:48:57

Hi Luc,
wo genau verhält sich xl9 anders als mein 2010er? Letzteres verhält sich wie beschrieben - nix frei erfunden :-)

Ich hatte nicht geschrieben, dass der Suchbegriff in einer verbundenen Zelle nicht gefunden würde, sondern
"Wenn E7:E8 verbunden sind, wird E7 gefunden"
Spannend ist, wie es dann weiter geht:
"Beim Weitersuchen meint Excel dann ... Resultat: Keine weitere Fundstelle ..."

Da sah ich einen Unterschied zwischen verbundenen und nicht verbundenen Zellen:
im Verhalten nach dem Finden. Gibt es diesen Unterschied in xl9 nicht?

Ich wäre dir dankbar, wenn du das mit dieser xl-Version testen würdest.
Nicht unwichtig: Der Suchbegriff sollte im Blatt nur einmal vorkommen.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: Ja, xl9 findet alle Sichtbaren, wenn nur ... von: Luc:-?
Geschrieben am: 05.02.2012 00:34:09

eine Auswahl getroffen wurde, die auch eine VerbundZelle, egal wie groß, sein kann, Erich.
Es ist dabei wirklich völlig schnurz, ob momentan eben 1 Verbund- oder 1 beliebige EinzelZelle ausgewählt ist. Das ist in der VBE-Hilfe auch missverständl bzw falsch dargestellt oder übersetzt, denn die VBA-Methode Find fktt im Zusammenspiel mit FindNext bzw FindPrevious unter xl9 ganz genauso → hier kommt's dann eher auf die richtige Parametrierung als darauf an, ob im Suchbereich auch Verbund­Zellen vorliegen.
Weil ich deine und Franz' Anmerkungen zu dem Thema nicht so recht glauben wollte, hatte ich das bereits ausprobiert als ich dir dann geschrieben habe. Inzwischen habe ich auch eine (VBA-)AW an Franz vorbereitet und 2 verschiedene kurze BspPgmm geschrieben, die wie folgt und auch in deren Kopf angegeben fktn:
1. Nach Find-Methode:
Es werden nur die auf dem Blatt sichtbaren Suchbegriffe, aber auch alle, gefunden.
2. Mit xlFkt Match:
Es werden grundsätzl alle Standorte gefunden, egal, ob sichtbar oder (unsichtbar) in VerbundZellen oder ausgeblendeten Zeilen/Spalten versteckt.
Wenn diese SubProzz unter höheren xlVss nicht wie beschrieben fktn, ist mir das ein Beweis dafür, dass MS Bewährt-Nützliches zugunsten zumeist oberflächlicher Effekte geopfert hat und auch xl14 noch nicht die alten Qualitätsstandards wiedererreicht hat.
Die Prozz wdn in meiner AW an Franz zu finden sein.
Gruß + schöSo, Luc :-?


  

Betrifft: Unterschiede von: Erich G.
Geschrieben am: 05.02.2012 11:04:01

Hi Luc,
zum Teil reden wir wohl einfach über verschiedene Aspekte oder Themen.
Ich versuchs mal zu simplifizieren - das geht am ehesten via Code:

Sub TestErich()
   With Worksheets.Add
                  Debug.Print "1 - E7 allein"
      .Range("E7").Value = "abc"
      DoIt 1, True
      DoIt 1, False

                  Debug.Print "2 - weitere belegte Zelle"
      .Range("C3").Value = 4711
      DoIt 2, True
      DoIt 2, False

                  Debug.Print "3 - weitere Fundstelle"
      .Range("C3").Value = "abc"
      DoIt 3, True
      DoIt 3, False
   End With
End Sub

Sub DoIt(nr As Long, merg As Boolean)
   Dim C As Range, sAdr As String
   
   With ActiveSheet
      .Range("E7:E8").MergeCells = merg
      Set C = .UsedRange.Find("abc", , xlValues)
      If C Is Nothing Then
         Debug.Print 10 * nr + (2 + merg) & "1 " & _
            IIf(merg, "verbunden", "einzeln") & " - nicht gefunden"
      Else
         Debug.Print 10 * nr + (2 + merg) & "2 " & _
            IIf(merg, "verbunden", "einzeln") & " - gefunden in " & C.Address
         sAdr = C.Address
         Do
            Set C = .UsedRange.FindNext(C)
            If C Is Nothing Then
               Debug.Print 10 * nr + (2 + merg) & "1 " & _
                  IIf(merg, "verbunden", "einzeln") & " - nicht wiedergef."
               Exit Do
            Else
               Debug.Print 10 * nr + (2 + merg) & "1 " & _
                  IIf(merg, "verbunden", "einzeln") & " - wiedergef. in " & C.Address
            End If
         Loop While C.Address <> sAdr
      End If
   End With
End Sub
Bei mir wird ausgegeben:
1 - E7 allein
111 verbunden - nicht gefunden
122 einzeln - gefunden in $E$7
121 einzeln - wiedergef. in $E$7
2 - weitere belegte Zelle
212 verbunden - gefunden in $E$7
211 verbunden - nicht wiedergef.
222 einzeln - gefunden in $E$7
221 einzeln - wiedergef. in $E$7
3 - weitere Fundstelle
312 verbunden - gefunden in $E$7
311 verbunden - wiedergef. in $C$3
311 verbunden - wiedergef. in $E$7
322 einzeln - gefunden in $E$7
321 einzeln - wiedergef. in $C$3
321 einzeln - wiedergef. in $E$7
Da sehe ich einen Unterschied zwischen Verbund und Einzeln bei "1 - E7 allein" und "2 - weitere belegte Zelle".
Bei "3 - weitere Fundstelle" verhalten sich Find/FindNext identisch.

Das alles in XL2010. Wie ist es in xl9(2000) ? Jetzt bin ich gespannt auf deine Analyse! :-)

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: Das mag sein, ... von: Luc:-?
Geschrieben am: 05.02.2012 15:29:46

…Erich,
denn dein Test führt unter Xl9 zum selben Ergebnis! Es hat sich also nichts geändert, was ich schon nach Lauf meiner TestPgmm unter Xl12 vermuten musste.
Dann ist das Ganze wohl nur eine Interpretationsfrage der Tests und dessen, was eigentl erreicht wdn soll und wie man das am besten bewerkstelligt. Debug.Print ist letztlich nur für den PgmEntwickler gedacht, dem Endnutzer muss Anderes geboten wdn. Und das liefert die von mir genannten Ergebnisse, die bis auf die genannte irreguläre Ausnahme (zuerst von Franz festgestellt) den Schluss zulassen, dass VerbundZellen keinen nennenswerten Einfluss auf das Methoden-Triple Find/FindNext/FindPrevious haben, es sei denn, man sucht nach verborgenen Inhalten.
Gruß + schöSoAb, Luc :-?


  

Betrifft: AW: FindNext und verbundene Zellen von: fcs
Geschrieben am: 03.02.2012 02:04:20

Hallo Reinhard,

das Problem hängt schon mit den verbundenen Zellen zusammen.

Gibt es den gesuchten Begriff nur einmal im zu durchsuchenden Zellbereich in verbundenen Zellen, dann wird mit Find Next -warum auch immer (die Microsoft-Götter haben sich da wohl etwas Spezielles einfallenlassen, um die Anwender zu irritieren)- nicht die wie sonst üblich die 1. gefundene Zelle zurückgegeben, sondern es wird nichts gefunden. Also ist C = Nothing. Wenn C = Nothing ist, dann ergibt C.Address einen Fehler. Durch Auftrennung der Prüfungen auf 2 Zeilen vermeidest du den Fehler.


In der 2. Datei hast du den den Zufall, dass die UsedRange nur aus den verbundenen Zellen besteht. Da findet das Makro die Zelle garnicht erst - in Tabelle2 wird keine Fundstelle eingetragen. Das Makro wird dann sofort beendet und durchläuft nicht die Do-Loop-Schleife.
Wenn du in einer anderen Zelle auch noch einen Wert einträgst tritt das Problem auch auf.

Also gilt: Vermeide verbundene Zellen oder Holzauge sei wachsam.

Gruß
Franz


  

Betrifft: AW: FindNext und verbundene Zellen von: Reinhard
Geschrieben am: 03.02.2012 19:10:07

Hallo Franz,

auch dir wie Erich herzlichen Dank.

Verbundene Zellen weglassen geht leider nicht, Mappe ist nicht von mir.
Ist eine Anfrage in einem anderen Forum wo jmd. viele xls-Dateien nach einem Suchwert durchsuchen will
nach einem Suchwert und dabei von Fundstelle zu Fundstelle hüpfen will um je nach nach seinem Gusto
den Suchwert in der Zelle abzuändern oder auch nicht.

Es war leichtsinnig von mir mich da reinzuknien/zu melden, naja, ich hatte die Problematiken unterschätzt
sonst hätte ich ihm geraten einen Auftragsprogrammierer zu beauftragen.
Egal, an so Aufgaben kann ich üben=lernen.
Und ich habe es auch geschafft die Codes dafür zu basteln und sie liefen *freu*

Bis dann halt ein neuer Satz Testmappen von ihm hochgeladen wurden, da waren halt in der einen mappe
diese verbundene Zellen drin. Und in den Originaldateien hat er noch viel mehr verb. Zellen.

Ich habe dann den relevanten Codeteil extrahiert und selbstlauffähig gemacht und getestete und getsetet und getestet.
Como siempre dabei alles Mögliche ausgetestet aber nicht das entscheidende :-(
So fragte ich hier an, Rest ist Geschichte...

Gruß
Reinhard


  

Betrifft: Ich kann, was du schreibst, für Xl9 nicht ... von: Luc:-?
Geschrieben am: 05.02.2012 02:32:15

…bestätigen, Franz!
Entweder ist Xl in späteren Vss „verschlimmbessert” worden oder Find & Co haben jetzt veränderte Konditionen bzw wdn/wurden falsch parametriert. FindNext dürfte grundsätzl den nächsten Wert nach dem mit Find gefundenen finden, egal, ob in einer Verbundzelle oder anderswo, HptSache sichtbar! Das gilt auch für eine einzelne Fundstelle als VerbundZelle, egal, ob zuvor schon ausgewählt oder nicht. Bei weiteren Fundstellen fktt das auch problemlos. Das meinst du wohl mit … wie sonst üblich die 1. gefundene Zelle zurückgegeben …. Bestätigen kann ich nur deine Aussage bzgl UsedRange nur aus den verbundenen Zellen, allerdings findet FindNext hier die Zellen, wenn der ganze UsedRange ausgewählt wurde. Find und FindNext im üblichen Zusammenspiel finden aber hier ebenfalls alle sichtbaren Zellwerte. Ich glaube deshalb nicht, dass das hptsächlich der VerbundZellenProblematik geschuldet ist.
Hier mal noch 2 BspCodes (auch für Erich & Reinhard), die auch mal unter Vss>xl9 getestet wdn sollten. Unter xl9 laufen sie problemlos, übrigens auch unter xl12!

Rem Findet nur sichtbare Zellen, d.intern angegeb Konst enthalten
Sub SeekNfind()
    Const suBgr As Variant = "xxx"
    Dim aSh As Worksheet, sfErg As Range
    On Error Resume Next
    Set aSh = ActiveSheet
    With aSh.UsedRange
        Set sfErg = .Find(suBgr, , xlValues, xlWhole, xlByRows)
        Do: If MsgBox(sfErg.Address(0, 0), vbInformation + vbOKCancel, _
                      suBgr & " gefunden in ...") = vbCancel Then Exit Do
            Set sfErg = .FindNext(sfErg)
        Loop While Not sfErg Is Nothing
    End With
    Set aSh = Nothing: Set sfErg = Nothing
End Sub

Rem Findet alle Zellen (auch versteckte), d.int angegeb Konst enthalt
Sub FindAll()
    Const suBgr As Variant = "xxx"
    Dim cix As Long, six As Long, aSh As Worksheet, sfErg As Range, xr As Range
    On Error Resume Next
    Set aSh = ActiveSheet
    For Each xr In aSh.UsedRange.Rows
        six = 0: cix = 0: cix = WorksheetFunction.Match(suBgr, xr, 0)
        Do While CBool(cix)
            six = six + cix: Set sfErg = xr.Cells(six)
            If MsgBox(sfErg.Address(0, 0), vbInformation + vbOKCancel, _
                      suBgr & " gefunden in ...") = vbCancel Then Exit Do
            cix = 0: cix = WorksheetFunction.Match(suBgr, _
                           Range(xr.Cells(six + 1), xr.Cells(xr.Cells.Count)), 0)
        Loop
    Next xr
    Set aSh = Nothing: Set sfErg = Nothing
End Sub
Gruß + schöSo, Luc :-?


Beiträge aus den Excel-Beispielen zum Thema "FindNext und verbundene Zellen"