Frage von Iamiam, 44

Excel-VBA ZelleListe von gestückeltem Bereich: ist das ein Bug oder mache ich was falsch?

Hallo VBA'ler, ich wollte einen Bereich an zufälligen Orten mit Werten füllen, ohne dass ein schon gesetzter Wert überschrieben wird (ohne Einzelfallprüfung).
Ich habe dazu folgendes Makro geschrieben:

  • Option Explicit: Public N As Integer
  • Sub Zellnummerbelegen()
  • Dim Leere As Range, ZufZ As Double
  • ZufZ = Rnd() 'festschreiben, damitmehrfach abrufbar
  • N = N + 1 'damit jedesmal kontrolliert ein anderes Zeichen gesetzt wird
  • Set Leere = Range("FüllBereich").SpecialCells(xlCellTypeBlanks)
  • Debug.Print Leere.Cells.Count & " " & Leere.Address & " ===> " & Leere.Cells(ZufZ * Leere.Cells.Count + 1).Address
  • Leere.Cells(ZufZ * Leere.Cells.Count + 1) = Chr(N + 35)
  • End Sub 'zur Aufzeichnung 01.10.2016 23:55

VBA zerlegt nun brav den anfangs einheitlichen Bereich zuverlässig in Teilbereiche und zählt richtig die verbliebenen leeren Zellen im Gesamtbereich Leere (immerhin erstaunlich!).
Beim Beschreiben dieser leeren Zellen werden aber sowohl teilweise Adressen ausserhalb gefüllt wie auch schon gefüllte überschrieben
Erst nach 18 Schritten war der 9-zellige Bereich (B3:D5) gefüllt und dadurch das Makro blockiert.
Hier das Protokoll (fett: ausserhalb, keine Veränderg der Blanks: desgl. oder Überschreibung):
9 $B$3:$D$5 ===> $C$3 ' ist identisch mit Füllbereich
8 $B$3,$D$3,$B$4:$D$5 ===> $B$5
7 $D$3,$B$3:$B$4,$C$4:$D$5 ===> $D$8
7 $D$3,$B$3:$B$4,$C$4:$D$5 ===> $D$6
7 $D$3,$B$3:$B$4,$C$4:$D$5 ===> $D$5
6 $B$3:$B$4,$D$3:$D$4,$C$4:$C$5 ===> $B$4
5 $B$3,$D$3:$D$4,$C$4:$C$5 ===> $B$7
5 $B$3,$D$3:$D$4,$C$4:$C$5 ===> $B$8
5 $B$3,$D$3:$D$4,$C$4:$C$5 ===> $B$6
5 $B$3,$D$3:$D$4,$C$4:$C$5 ===> $B$3
4 $D$3:$D$4,$C$4:$C$5 ===> $D$6
4 $D$3:$D$4,$C$4:$C$5 ===> $D$5
4 $D$3:$D$4,$C$4:$C$5 ===> $D$5
4 $D$3:$D$4,$C$4:$C$5 ===> $D$5
4 $D$3:$D$4,$C$4:$C$5 ===> $D$4
3 $D$3,$C$4:$C$5 ===> $D$4
3 $D$3,$C$4:$C$5 ===> $D$3
2 $C$4:$C$5 ===> $C$5
1 $C$4 ===> $C$4

Vielleicht weiß einer ja was Besseres, was in akzeptabler Zeit ALLE Zellen füllt.
Danke für Eure Hilfe, ich kann aber erst am Abend wieder Stellung dazu nehmen

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Oubyi, Community-Experte für Excel, 24

Ich habe jetzt keine Lust, mich da intensiv reinzuarbeiten, aber wenn Du mit Leere schon eine Range für alle leeren Zellen hast, könntest Du mit:
Dim Zelle as Range
....
For each Zelle in Leere
...
Next Zelle

eine Schleife dadurch bauen.
Vielleicht hilft Dir das ja weiter.
Kann aber auch sein, das ich das ganze falsch verstanden habe.

P.S.:
Wenn Du den VBA-Code hier direkt reinkopierst, markierst und dann oben auf "Codebeispiel einfügen" klickst, wird er sehr schön formatiert hier eingefügt.

Kommentar von Iamiam ,

Codebeispiel in GF: Ich mag das nicht, weil ich da nie die ganze Zeile sehe sondern bei längeren Befehlen immer scrollen muss. Auch so kann man den Code kopieren und ins Modul einfügen (die Punkte verschwinden, die gesetzten Umbrüche bleiben und bei längeren Befehlen hat man alles auf einmal im Blickfeld)

Zur Sache: ich rufe das Makro für jede Zelle per zugewiesenem Knopf (formatiertes Textfeld) neu auf. Der Kombibereich Leere wird also jedesmal neu erstellt, und zwar richtig, siehe Protokoll. Auch die Anzahl der Blanks stimmt (linke Zahl). Ich weiß also nicht, wo ich da eine Schleife einbauen soll und was die bewirken könnte.

Aber das statistische Ansprechen der ZelleListe dieses Kombibereichs (also der Blanks) liefert falsche Positionen, die häufig sogar ausserhalb aller Teilbereiche liegen und innerhalb auch Nichtleere betreffen -- das ist mir ein Rätsel!

Und obendrein eines, für das ich keine Umgehungsmöglichkeit sehe ausser über die statistische Benennung aller Zellen des Bereichs und nochmalige Wahl, wenn schon gefüllt.

Am Anfang wäre das kein Problem, aber bei größeren Bereichen, zB 900Zellen(30x30) braucht das für die letzte Zelle im Durchschnitt 900x so lang wie für die erste, das wollte ich vermeiden

Kommentar von Oubyi ,

Jetzt muss ich mich erstmal entschuldigen.
Ich hatte nicht bemerkt, dass diese Frage von Dir stammt, sonst hätte ich mir die vorschnelle Antwort gespart.
Ich schau mir das jetzt mal genauer an und melde mich wieder.

Kommentar von Iamiam ,

keine Ursache.

ich hab auch noch rumprobiert  und bin zu dem Ergebnis gekommen: es ist ein Bug!

Solange die angesprochene Zelle im ersten der jeweils gestückelten Bereiche (wie dokumentiert) liegt, ist die Ansprache richtig. Liegt sie aber in einem weiteren der Teilbereiche, wird sie falsch. Wie es allerdings dazu kommt, dass sogar Zellen ausserhalb angesprochen werden und das auch i*wie mit der Spalte zusammenzuhängen scheint, weiß ich weiterhin nicht, ist aber auch nicht so wichtig: falsch ist falsch!

Wo könnte man sowas melden? (vllt ist es ja auch absichtlich nicht korrigiert um genau sowas und Ähnliches nicht zuzulassen? (MS hat ja sicher kostenpflichtige Spezialanwendungen für solche Fälle, die man so leichter selbst mit xl lösen könnte...  Ich traue MS ALLES zu!)

Kommentar von Oubyi ,

Ich bin gerade dabei das ausführlich auszutesten und kann erstmal sagen, dass ich das für eine Bug halte. Anscheinend kann Excel mit einer unzusammenhängenden Range und .Cells(n) nicht richtig umgehen. Z.B. wird mit:

Sub test()
Range("A1:A2,B1:C1").Cells(3).Select
End Sub

Die Zelle A3 selektiert!

Und jetzt (ca. 1 h später) habe ich zumindest ein Workaround für Dich:

Option Explicit
Public N As Integer

Sub Zellnummerbelegen()
Dim Leere As Range, ZufZ As Double
Dim Zelle As Range
Dim Counter As Integer
Counter = 0

ZufZ = Rnd() 'festschreiben, damitmehrfach abrufbar
N = N + 1 'damit jedesmal kontrolliert ein anderes Zeichen gesetzt wird
Set Leere = Range("FüllBereich").SpecialCells(xlCellTypeBlanks)

Debug.Print Leere.Cells.Count & " " & Leere.Address '& " ===> " & Leere.Cells(ZufZ * Leere.Cells.Count).Address
Debug.Print "Z " & ZufZ * Leere.Cells.Count

For Each Zelle In Leere
Counter = Counter + 1
Debug.Print "C " & Counter
If Counter > (ZufZ * Leere.Cells.Count) Then
Zelle = Chr(N + 35)
Exit For
End If
Next Zelle
End Sub 'zur Aufzeichnung 01.10.2016 23:55


Das ist zwar von der Performance her wegen der - doch noch zur Anwendung kommenden - For Each-Schleife nicht wirklich schön, aber funktionieren sollte es eigentlich. Teste mal genau.

P.S.:
Ich sehe gerade, unsere Kommentare haben sich überschnitten.I
Ich hatte Deinen noch nicht gelesen.
Mache ich jetzt (:o)


Kommentar von Oubyi ,

ich hab auch noch rumprobiert  und bin zu dem Ergebnis gekommen: es ist ein Bug!

Dito

Kommentar von Iamiam ,

danke, muss ich erst mal verarbeiten, auch die Anregung von Ninombre (aber erst, nachdem ich für meine Regeneration gesorgt habe, brauche nach dieser kurzen Nacht ein ausgiebiges Mittagsschläfchen!)

Kommentar von Iamiam ,

Hi Oubyi, habe Dein µ nochmal leicht verändert und es funktioniert hervorragend! Bin begeistert! Keine Doppeleinträge, keine Einträge ausserhalb und was die Performance angeht:
während in der üblichen Version mit Abfrage jeder Zelle ob noch leer,
ein mit dem Füllfortschritt reziprokes Anwachsen die Laufzeit drastisch
verlägert,

reduziert sich die Schleife bei Deiner Version jedesmal um mindestens 1: ich hab die 9 Zellen mit 30 bzw 31 Anläufen (ungenaue Statistik) füllen können. (hab das aber erst kapiert, nachdem ich mir die Direktprotokolle genauer angeschaut hab).

Ich schreib hier nochmals meine Variation Deiner Fassung rein, falls Dich das interessiert (ich spare übrigens hartnäckig dargestellte Codezeilen, um nicht ständig scrollen oder Fenster wechseln zu müssen):

  • Option Explicit: Public N
  • Sub ZellNummerBelegenOubyiVar() 'funktioniert wie gewünscht!
  • Dim Leere As Range, ZufZ As Double, Zelle As Range, Counter As Integer
  • Counter = 0: ZufZ = Rnd(): N = N + 1
  • Set Leere = Range("FüllBereich2").SpecialCells(xlCellTypeBlanks)
  • For Each Zelle In Leere
  •  Counter = Counter + 1: Debug.Print "_Counter: " & Counter & " " & Zelle.Address
  •   If Counter > (ZufZ * Leere.Cells.Count) Then
  •   Zelle = Chr(N + 39): If Leere.Cells.Count = 1 Then MsgBox "alle Zellen gefüllt": Debug.Print "BCounter: " & Counter & " " & Zelle.Address: Exit Sub
  •   Exit For
  •  End If
  • Next Zelle
  • End Sub ' N jetzt mit 0+1+39=( gestartet, da ' unsichtbar


Kommentar von Oubyi ,

Freut mich, dass es klappt.
Danke fürs

Bei längeren Codezeilen stört mich das Scrollen natürlich auch. Aber dafür wird die Strukturierung durch die Einrückungen sehr schön übernommen.
Wie machst Du das, dass Du jede einzelne Codezeile als Aufzählung hinbekommst. Kopierst Du jede Zeile einzeln ein?
Den kompletten Code auf einmal so einzukopieren, dass jede Zeile ihren Punkt bekommt, bekomme ich nicht hin.

P.S.: Über das Hochkomma war ich auch schon "gestolpert".

Kommentar von Iamiam ,

ich kopiere den gesamten Code hier ein, definiere die erste Zeile als Aufzählung, geh ans Ende, die nächste anhängen mit Del, an selber Stelle wieder Enter: damit hat die nächste ihren Aufzählpunkt. Zeile für Zeile wiederholen, geht aber schneller, als es sich hier liest.

Beim Fragenstellen gilt wie bei den PN oder eigentlich allem anderen noch der alte Editor, was jedesmal eine Umstellung bedeutet. Der Aufzählpunkt hier war dort ein -

Was mich noch mehr stört, dass das Zeichen < das Einfügen aus der Zwischenablage verhindert, auch im Code (und ich hab '< immer als spezifisches Zeilenkommentarkennzeichen verwendet und wie oft kommt < oder <> im normalen Code vor. Ich überlege inzwischen immer, ob ich das nicht besser durch ein >= ersetzen kann, also ob es möglich ist, den Vergleich andersrum zu schreiben, lauter im Prinzip unnötige Klimmzüge!) Ausserdem stört mich gewaltig, dass ich seit längerem einen Text aus einem Textfeld nicht mehr hier reinbringen kann, dass ich auch längrere Texte erst mal in die Bearbeitungszeile einer Zelle kopieren muss, dann erst dort wieder rausholen und hier einfügen.

Aber da bei GF jede Anregung bisher nur zu -gut gemeinten ;)- Verschlimmbesserungen geführt hat, sag ich dort lieber nichts mehr....

Kommentar von Oubyi ,

Die Kümmernisse mit dem Editor sind ja leider ein Dauerthema hier. Da halte ich mich inzwischen auch raus.

Das mit der Aufzählung klappt jetzt bei mir auch, werde ich vlt. noch drauf zurückkommen. Danke für die Erläuterung.
Noch einfacher/ schneller geht es imho, den Cursor VOR die NÄCHSTE Zeile zu setzen und dann mit BACKSPACE & ENTER weiter zu machen. Das geht raz/faz.

Expertenantwort
von Ninombre, Community-Experte für Excel, 13

Ich kann bisher leider nur beitragen, dass es bei mir in gleicher Weise nicht funktioniert, Excel 2010. Ab dem 3. oder 4. Durchlauf wird auch außerhalb des Bereichs geschrieben - ab ddiesem ersten "Fehleintrag" kommen die Ergebnisse bei mir nur noch in einer Spalte.

Es scheint mit dem "gestückelten Bereich" recht schnell zu Fehlern zu kommen, denn auch wenn man nur eine Zeile A1:D1 als Range definiert tritt das Phänomen auf.

Wenn ich testweise den Range mal in einer for schleife aufgebaut habe

For Each zelle In gesamtbereich
If zelle.Value = "" Then Set freierbereich = Application.Union(freierbereich, zelle)
Next zelle

Getestet bei einem Bereich A1:D100 und 300 Wiederholungen werden die Treffer alle innerhalb des zulässigen Range geschrieben (soweit gut), allerdings werden dennoch Werte überschrieben, ca. 1/3, also nicht akzeptabel.

Mir fällt nichts wirklich anderes ein, als dann doch auf value="" zu prüfen. Die Performance lässt sich sicherlich optimieren, wenn man nicht den gesamten Bereich in der Schleife prüft, sondern erst in der "Zielzeile"

Kommentar von Iamiam ,

danke für Deine Mühe. Vllt gibt das einen neuen Denkanstoß.

Hab zum Bug noch was in einem Kommentar zu Oubyi geschrieben

Vor kurzem hatte jemand hier eine Frage mit Teilbereichen eingestellt: kannst du Diuch erinnern, welche?

Kommentar von Oubyi ,

Vor kurzem hatte jemand hier eine Frage mit Teilbereichen eingestellt: kannst du Diuch erinnern, welche?

Meinst Du die?:

https://www.gutefrage.net/frage/summewenn-und-teilergebnis-kombinieren-aber-wie-...

Kommentar von Iamiam ,

danke, nein, die wars nicht. Nicht Teilergebnis sondern Union(Teilbereiche). War dort zwar völlig überflüssig, aber da wurde eine Syntax verwendet und ein Ausdruck, die mir neu waren. Wollte mirs notieren und habs dann doch vergessen.

Aber für dieses Problem hier ist das ohnehin obsolet, ich bin mit Oubyis Ansatz - leicht modifiziert-  fabelhaft zurechtgekommen! Erstaunlich, dass sich auch in schwierigsten Fällen immer wieder eine Lösung finden lässt!

Kommentar von Iamiam ,

wenn mans nicht mehr braucht, findet man es:

www.gutefrage.net/frage/excel-zufall--es-passiert-immer-der-gleiche-zufall-?

Es war die Verwendung von Areas zum Bestimmen gefüllter Zellen, an das ich mich nebulös erinnern konnte.

So, und jetzt endlich: alles abhaken! Uff!

Kommentar von Oubyi ,

lamiams Idee mit:

Set Leere = Range("FüllBereich").SpecialCells(xlCellTypeBlanks)

funktioniert aber imho sehr gut.
Nur, dass .Cells(n) fehlerhaft arbeitet.
Aber immerhin spart man sich dadurch immer den gesamten Bereich durchlaufen zu müssen.

Keine passende Antwort gefunden?

Fragen Sie die Community