Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1484to1488
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

in bestehenden Code benannten Bereich einfügen

in bestehenden Code benannten Bereich einfügen
27.03.2016 20:48:49
MB12

Hallo zusammen,
nachdem mir Michael einen tollen Code "gezaubert" hat, den ich dann noch erfolgreich erweitert habe, fehlt mir noch eine Kleinigkeit. Ich starte einen Beitrag, da Michael einige Tage nicht da ist:
Ich möchte jeweils in die damit neu erzeugten Blätter (ca 30) einen benannten Bereich aus einem anderen Tabellenblatt einfügen. Der Bereich heißt "krit" (siehe unten.) Das wär's eigentlich schon. Der Code:
Option Explicit

Sub Zusatzblatt2()
Dim vorhSh As String, shName As String
Dim actSh As Worksheet, laufSh As Worksheet
Dim actShNr&, neuesShNr&, maxShNr&, i&, z&, maxzeile&, von&
Set actSh = ActiveSheet
vorhSh = "!"   ' Ausrufezeichen kann NICHT im Blattnamen vorhanden sein...
For Each laufSh In Worksheets: vorhSh = vorhSh & laufSh.Name & "!": Next
maxzeile = actSh.Range("A" & actSh.Rows.Count).End(xlUp).Row
von = 2
For z = 2 To maxzeile
If actSh.Range("A" & z) = "" Then
shName = Mid(actSh.Range("A" & von).Value, 7)
If shName = "" Then: MsgBox "Phase_XXX???": Exit Sub
If InStr(vorhSh, "!" & shName & "!") = 0 Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = shName
vorhSh = vorhSh & shName & "!"
End If
actSh.Range("A" & von & ":C" & z).Copy Sheets(shName).Range("B5")
Sheets(shName).Range("B5:D" & z - von + 1).FormulaLocal = "=" & actSh.Name & "!A" & von
von = z + 1
'ab A25 den benannten Bereich "krit" (A1:B23) aus Blatt "Bild" einfügen
Columns("A:A").ColumnWidth = 7
Columns("B:B").ColumnWidth = 15
Columns("C:C").ColumnWidth = 20
Columns("D:E").ColumnWidth = 10
Rows("25:90").RowHeight = 20
ActiveWindow.DisplayZeros = False
Range("A1").Value = ActiveSheet.Name & "2"
Range("A1").Font.ColorIndex = 15
End If
Next
End Sub

Danke schon jetzt sehr herzlich.
Margarete

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Sheets("Bild").Range("krit") und 'von' um ...
28.03.2016 11:46:24
Luc:-?
…Zeilenzahl erhöhen, falls der Zyklus anschließend noch weiterlaufen soll, Margarete;
allerdings ist diese Art Zyklus nur dann geeignet, wenn maxzeile schon vorab alle Einfüge­Zeilen berücksichtigt, denn sonst führt eine Erhöhung von von nur zu einem vorzeitigen Erreichen des ZyklusEndes!
Die Bedingung für den Beginn dieser Einfügung ist If von = 25 Then.
Gruß & schönOstMo, Luc :-?

AW: Sheets("Bild").Range("krit") und 'von' um ...
28.03.2016 13:06:54
MB12
Hallo Luc,
gut, dass du mich auf den Zyklus aufmerksam gemacht hast. Im Code stand ursprünglich jeweils "B1"
actSh.Range("A" & von & ":C" & z).Copy Sheets(shName).Range("B1")
Sheets(shName).Range("B1:D" & z - von + 1).FormulaLocal = "=" & actSh.Name & "!A" & von
von = z + 1
dann habe ich ausprobiert, ob ich den Bereich auch ab Zeile 5 einfügen kann, und das ging problemlos. Bei B25 kann es nicht funktionieren - Mist.
Erst jetzt ist mir aufgefallen, dass ich Euch gestern Abend eine falsche Info gegeben habe:
Der "benannte Bereich" soll oben in das Blatt kommen, also von A1 bis A23. Die im Zyklus erzeugten Einträge sollen darunter, also ab A25 beginnen. Mea culpa - man sollte spät abends nicht mehr herumbasteln.
Deshalb eine Beispieldatei. Code ist in Modul3; gewünschtes Ergebnis ist im Beispielblatt.
https://www.herber.de/bbs/user/104630.xlsm
Könntest du das evt. anpassen und das neue Code-Schnipsel einfügen? Ich bin an meine Grenzen gestoßen, nachdem all meine Versuche Fehler produziert hatten.
Liebe Grüße zum Ostermontag und vielen Dank im voraus
Margarete

Anzeige
AW: Sheets("Bild").Range("krit") und 'von' um ...
28.03.2016 17:07:28
Michael
Hi zusammen,
ich hab's mir mal angesehen...
Aaalso, zu meiner "Ehrenrettung", wenn der Kollege Luc:-? schon (auch) hier zugange ist:
Ich mag eigentlich keine Algorithmen, die ALLE Zellen einer Spalte in einer Spalte durchlaufen, wie hier die Zeile: For z = 2 To maxzeile
Das hätte man eleganter lösen können, wären da nicht übergebliebene Formeln, z.B. in Bereiche!A19 - rot markiert.
Aber egal. Das Kopieren der Bereiche dauert länger als die Schleife...
Der Punkt ist, daß bei der Zeile ...

Sheets(shName).Range("B5:D" & z - von + 1).FormulaLocal = "=" & actSh.Name & "!A" & von

die Angabe z - von + 1 nicht mehr stimmt, wenn die Geschichte nicht in B1 (eins!) eingefügt wird. Deshalb habe ich eine Konstante B_Off eingeführt, die man nur am Beginn der sub ändern muß, dann paßt es:

Const B_Off = 25
...code...
laufSh.Range("B" & B_Off & ":D" & B_Off - 1 + z - von).FormulaLocal = ...
^^^^^          ^^^^^^^^^^^^^^^^^^^^ 
Grete, sieh Dir mal unter "Formeln" den Namensmanager an: da siehst Du lauter "Gerümpel" aus ehemals verwendeten Namensbereichen. Das ist nämlich nicht soooo einfach, denn man kann Namen unterschiedlich definieren, nämlich so, daß sie nur einmal in einer Datei vorkommen können ODER einmal pro Blatt.
Kopieraktionen mit NAMENSbereichen können sich insofern als kritisch erweisen.
Deshalb habe ich wiederum eine Konstante eingeführt, die zum Kopieren des "Bilds" verwendet wird:
Const Krit = "A1:G23"
...code...
Sheets("Bild").Range(Krit).Copy laufSh.Range("A1")
Die Datei: https://www.herber.de/bbs/user/104634.xlsm
Wenn alles zufriedenstellend läuft, muß man eigentlich nur noch das screenupdating auf false setzen.
Die Ostergaggala hamma übrigens gefallen - kling ja fast wie in S.
Schöne Grüße,
Michael

Anzeige
AW: Sheets("Bild").Range("krit") und 'von' um ...
28.03.2016 18:30:03
MB12
Hallo Michael, Luc und alle,
Michael: mit dir hatte ich heute gar nicht gerechnet! Was machst du an solch einem schönen Tag am Computer? Den geänderten Code werde ich ganz bestimmt noch durchsehen, aber - du wirst es kaum glauben - ich habe es nach Stuuuunden geschafft, nachdem ich auch bemerkt hatte, dass der Name zerschossen war.
Zum allgemeinen Amusement lade ich meine Lösungsdatei hoch:
https://www.herber.de/bbs/user/104635.xlsm
Geh auch mal in das Blatt "Code". Da habe ich versucht, deinen Code zu analysieren.
Allen Helfern meinen herzlichen Dank
Margarete

Anzeige
AW: Sheets("Bild").Range("krit") und 'von' um ...
30.03.2016 18:33:49
Michael
Hi Margarete,
das frage ich mich auch ...
Es scheint ja alles so zu tun, wie Du es Dir vorstellst - das ist ja mal richtig gut!
"Amüsieren" tu ich mich nicht: man tut halt, was man kann, und tastet sich so vor...
Ach, lustig ist es schon (aber halt nicht auf Deine Kosten), denn ich habe auch schon Programmcodes zur Analyse in eine Exceltabelle gesteckt (Du weißt schon, daß man VBA zeilenweise mit F8 durchgehen kann?!), und vermutlich hat jeder VBAler mal irgendwann eine Farbtabelle gestrickt.
Ich bin die Tage auf eine nette Geschichte zur Visualisierung von Code gestolpert ...
die Seite: http://www.iti.fh-flensburg.de/lang/algorithmen/pattern/bm.htm
die Datei: http://www.inf.fh-flensburg.de/lang/algorithmen/pattern/bmpreprocess.xls
... aber DIE Arbeit möchte ich mir nur ganz ausnahmsweise machen, hehe.
Schöne Grüße und happy exceling,
Michael

Anzeige
Analyse
31.03.2016 20:34:22
MB12
Hallo Michael,
nachdem ich eben in deinen Uploads war, verstehe ich endlich, wie das mit dem "zeilenweisen Durchgehen" funktioniert, danke dafür. Und in deiner letzten Nachricht habe ich das mit dem Screen updaten gelesen - das hatte ich vergessen, wird noch eingefügt. Frage dazu: Sehe ich es richtig, dass man das speziell für umfangreiche Schleifen benötigt (kein Flackern, Zeitersparnis)?
Mit Algorithmen habe ich relativ wenig am Hut; ich arbeite zu 90% ergebnisorientiert, sprich: Wenn ich vor einer Aufgabe stehe, versuche ich, die Eingangsbedingungen zu analysieren und dann das gewünschte bzw. erreichbare Ziel zu definieren, wobei ich mich selbst dazu verdonnere, mehr als einen Weg anzudenken (weil ich mich manchmal in einen Weg verbeiße).
Speziell beim Erstellen von Statistiken stelle ich immer wieder fest, dass man nicht über die Aussagekraft des Ergebnisses nachdenkt und schon Fehler in den einzubeziehenden Daten macht (typisch: Vergleiche der berühmten Äpfel mit Birnen), so dass ich ein eigenes Szenario entwickeln und vorschlagen muss oder auch sage, warum die Auswertung unnütz sein wird. Seitdem ich auch hier immer mehr gefordert werde, weiß ich, warum es dafür eigene Studiengänge gibt...
Mein Glück ist, dass ich einen wirklich tollen Chef habe, der gerade mal halb so alt wie ich ist, aber bereits so souverän, dass ich jederzeit Einwände geltend oder Gegenvorschläge machen kann. So macht das Arbeitsleben Spass!
Liebe Grüße
Margarete

Anzeige
AW: Analyse
01.04.2016 18:13:29
Michael
Hi Margarete,
das mit dem screenupdating ist so eine Sache...
Ich versuche, alles, was mit Berechnungen bzw. Datenschaufeln zu tun hat, in VBA zu machen, und zwar in Array-Strukturen. D.h., ich kopiere alle Daten erst in Array, bearbeite sie und schreibe das (oder ein) Array zurück. Dabei erfolgen keine Zugriffe auf einzelne Zellen, also passiert weder was am Bildschirm, noch werden Excels Berechnungen (application.calculation=xlmanual oder wie das wieder genau heißt) "angestoßen".
So ein Array-handling kannst Du Dir grad aktuell beim Sav ansehen
https://www.herber.de/forum/messages/1485380.html
Bei einem Test habe ich herausgefunden, daß bei dieser Vorgehensweise, wenn man sie denn strikt durchzieht, das Aus- und wieder Einschalten von screenupdating & Co ZUSÄTZLICHE Zeit frißt - natürlich ist diese Zeit zu vernachlässigen, das sind ein paar Mikrosekunden, ich erwähne es nur wegen des Prinzips...
In der Praxis geht halt nix über Ausprobieren, man kann ja auch mal eine Zeitmessung machen, etwa so wie hier: https://www.herber.de/bbs/user/104718.xlsm
Teste mal die buttons (bei BIG kannste in die Pause gehen)...
Was meinste?
Schöne Grüße,
Michael

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige