Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Mehrfachselektion von Sheets - Zeilen einfügen

Mehrfachselektion von Sheets - Zeilen einfügen
07.05.2013 09:12:23
Sheets
Guten Tag
Ich habe Dateien mir einer sehr grossen Anzahl Worksheets, bei denen ich gelegentlich Zeilen einfügen muss.
Mit untenstehendem Code wähle ich Tabellen aus, deren Name mit den gleichen zwei Zeichen (hier "02") beginnen. Das klappt.
Weshalb wird nach der Mehrfachselektion beim Einfügen nur im aktiven Worksheet die Zeile eingefügt und in in allen der Mehrfachselektion? Was muss ich ändern, damit dies bei allen Tabellen funktioniert?
Gruss, Peter
Option Explicit
Sub Zeilen_einfuegen()
Dim strLinks As Integer, x As Integer, y As Integer
y = 0
strLinks = "02"
For x = 1 To ThisWorkbook.Worksheets.Count
If Left(Worksheets(x).Name, 2) = strLinks Then
y = y + 1
Select Case y
Case 1
Worksheets(x).Select
Case Else
Worksheets(x).Select (False)   'ab 2. Tabelle bereits selektierte Tabelle nicht  _
deselectionieren
End Select
End If
Next x
If y  0 Then Cells(2, 1).EntireRow.Insert  'wenn keine Tabellen mit strLinks gefunden werden,  _
keine Aktion
End Sub

https://www.herber.de/bbs/user/85222.xlsm

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mehrfachselektion von Sheets - Zeilen einfügen
07.05.2013 09:42:33
Sheets
Hi,
es ist:
For x = 1 To ActiveWorkbook.Sheets.Count
ThisWorkbook.Worksheets.Count ergibt (bei mir) immer 1.
Grüße,
Klaus M.vdT.

Blödsinn von mir!
07.05.2013 09:46:16
mir!
Hi Peter,
vergiss mein letztes Post, da war ich umnachtet :-)
das hier funktioniert:
Option Explicit
Sub Zeilen_einfuegen()
Dim strLinks As Integer, x As Integer
strLinks = "02"
For x = 1 To ThisWorkbook.Worksheets.Count
If Left(Worksheets(x).Name, 2) = strLinks Then
Worksheets(x).Cells(2, 1).EntireRow.Insert
End If
Next x
End Sub
die Worksheets zu selektieren ist nicht notwendig!
Die antwort auf deine Frage ist:
weil dein Kommando
Cells(2, 1).EntireRow.Insert
nicht referenziert ist und sich damit nicht auf die selektierten, sondern nur auf das AKTIVE Workbook bezieht!
Grüße,
Klaus M.vdT.

Anzeige
AW: wie b Mehrfachselekt. referenzieren?
07.05.2013 09:52:13
Peter
Hallo Klaus
Vielen Dank.
Ich würde mir einen Geschwindigkeitsvorteil versprechen, wenn ich beispielsweise alle Monatstabellen (Februar hier = "02") mit einer Mehrfachselektion auswählen könnte und dann einmal die Zeile einfügen.
Wie muss ich die Mehrfachselektion referenzieren, damit alle selektierten Tabellen eingeschlossen sind?
Gruss, Peter

AW: wie b Mehrfachselekt. referenzieren?
07.05.2013 09:57:59
Klaus
Hallo Peter,
ungeprüft behaupte ich:
verzicht auf .select bringt mehr Geschwindigkeit als alles andere.
Ist das da oben dein ganzer Code? Wie viele Tabellen hast du denn, in die Zeilen eingefügt werden? Und wie lange läuft der (modifizierte) Code durch?
Bevor du dich jetzt in selektiererei versteifst und es daran gar nicht liegt, probier mal diesen Code (um das allseits beliebte GetMoreSpeed erweitert - das kannst du nicht nur hier, sondern auch woanders einsetzen!):
Option Explicit
Sub Zeilen_einfuegen()
Dim strLinks As Integer, x As Integer
GetMoreSpeed (True)
strLinks = "02"
For x = 1 To ThisWorkbook.Worksheets.Count
If Left(Worksheets(x).Name, 2) = strLinks Then
Worksheets(x).Cells(2, 1).EntireRow.Insert
End If
Next x
GetMoreSpeed (False)
End Sub
Public Static Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
Dim intCalculation As Integer
If Modus = True Then intCalculation = Application.Calculation
With Application
.ScreenUpdating = Not Modus
.EnableEvents = Not Modus
.Calculation = IIf(Modus = True, xlManual, intCalculation)
.Cursor = IIf(Modus = True, 2, -4143)
End With
End Sub

Grüße,
Klaus M.vdT.

Anzeige
AW: wie b Mehrfachselekt. referenzieren?
07.05.2013 10:08:03
Peter
Hallo Klaus
Danke.
Ich habe eine Datei pro Semester, also etwas über 180 Worksheets 0101 - 0630, dann noch ein paar zusätzliche Tabellen. Die Worksheets enthalten viel Daten. Früher konnte ich problemlos alle gut 180 Worksheets manuell selektieren, dann eine neue Zeile über alle Worksheets einfügen. In der Zwischenzeit mag Excel zwischendurch nicht mehr, das heisst, Excel steigt aus. Deshalb suche ich jetzt nach Lösungen, das häppchenweise zu machen.
Das Einfügen pro Worksheet habe ich bereits umgesetzt, jetzt dachte ich, dass ich möglicherweise etwas Zeit gewinne, wenn ich das Einfügen jeweils für ein paar Tabellen vornehmen kann.
Zudem interessiert mich grundsätzlich, wie ich bei einer Mehrfachselektion referenzieren muss, damit das beim Zeilen-Einfügen alle Tabellen erwischt werden.
Gruss, Peter
PS: Danke für den Offtopic-Hinweis
Gruss, Peter

Anzeige
AW: wie b Mehrfachselekt. referenzieren?
07.05.2013 10:34:51
Klaus
Zudem interessiert mich grundsätzlich, wie ich bei einer Mehrfachselektion referenzieren muss, damit das beim Zeilen-Einfügen alle Tabellen erwischt werden.
Hi Peter,
ich hab das mal makrorekordert (du sicher auch), aber bekomme es nicht auf "ohne select" verkürzt. Akademisches, grundsätzliches Interesse unterstütze ich immer, aber du bist auf dem Holzweg. Verzichte auf selects wo immer möglich.
Grüße,
Klaus M.vdT.

AW: OK, Danke! owT (ich verlasse den Holzweg :-))
07.05.2013 10:37:53
Peter

AW: Mehrfachselektion von Sheets - Zeilen einfügen
07.05.2013 09:53:21
Sheets
Hallo Peter,
Was soll denn die ganze .Select(ierei) ?
So:
Sub Zeilen_einfuegen()
Dim strLinks As Integer, x As Integer, y As Integer
Dim WS As Worksheet
strLinks = "02"
For Each WS In ThisWorkbook.Worksheets
If Left(WS.Name, 2) = strLinks Then WS.Cells(2, 1).EntireRow.Insert
Next WS
End Sub

Gruess Hansueli

Anzeige
leicht offtopic
07.05.2013 10:02:52
Klaus
Hi Peter,
etwas offtopic: ich würde feste Angaben, wie die 2 in diesem Befehl,
Left(WS.Name, 2)
möglichst vermeiden. Du prüftst ja auf StrLinks, also soll das Left auch so lang sein wie StrLinks
Left(WS.Name, Len(strLinks))
Mir ist schon klar, dass du auf Monate von 01-12 prüfst und das immer exakt 2 lang ist. Aber wenn du gleich variabel programmierst, hast du es später einfacher: Irgendwann musst du alle Blätter die mit "Einkauf" anfangen haben, dann erinnerst du dich "den Code hab ich doch schon"! und musst ihn nur noch kopieren.
Grüße,
Klaus M.vdT.

Anzeige
Da ist noch mehr Blödsinn drin, ...
07.05.2013 13:35:30
Luc:-?
…Leute (& Peter)!
1. Was soll zB das →
Dim strLinks As Integer und dann aber …
strLinks = "02" ?
Das beweist nur, das Peter von Variablendeklaration kA hat geschweige denn von UN, ob echt oder verballhornt (wie leider im Forum propagiert).
str wird von Vertretern der verballhornten PrimitivVariante der UN gemeinhin als Präfix zur Kennzeichnung jedweder Art von TextVariablen benutzt. Gleichzeitig wird diese Variable aber als ganzzahlig (also Zahl!) deklariert und ihr schließlich aber ein Text zugewiesen. Da der numerisch ist, wird er automatisch in die Ganzzahl 2 umgewandelt (sonst gäbe es hier den 1.Fehler!). Außerdem ist so etwas ohnehin Quatsch, denn bei fester Wertzuweisung verwendet man üblicherweise eine Konstante (um bei Peters Pseudo-UN zu bleiben Const strLinks As String = "02" oder Const intLinks As Integer = 2 → da es sich beim späteren Vgl aber um Benennungs- also Textteile handelt, wäre wohl Ersteres treffender).
2. Mit dieser Zykluskonstruktion kann man die betroffenen Blätter nur nacheinander aus- bzw anwählen, was ja auch ausreichen sollte, zumal man so leicht auf .Select verzichten kann. Anderenfalls müsste hier (wg Vgl) erst eine Liste der betroffenen Blätter erstellt wdn, wozu es auch eines Zyklus bedarf. Diese Liste kann man dann gleichzeitig ansprechen, selektieren oder gleich ändern.
Zu dem, was dann im hier gezeigten Zyklus steht, schweigt des „Sängers Höflichkeit“ lieber… ;->
Gruß Luc :-?

Anzeige
Manchmal hat auch der Dumme(?) Glück :-)
07.05.2013 14:00:16
Peter
Hallo Luc
Manchmal hat auch der Dumme(?) Glück :-)
Zuerst wollte ich tatsächlich mit einer Integer Variable arbeiten, habe mich dann für eine String Variable entschieden, jedoch nur den Namen und nicht die Deklaration geändert.
Aber ich hatte Glück.
strLinks = "02" gibt 2
und meine Tabellen vom Februar 0201, 0202, 0203 geben mit Left(Worksheets(x).Name, 2) "fast" das selbe, nämlich 02 - und für Excel ist hier 2 = 02 wahr.
Was ich noch nicht verstanden habe: Was ist eine UN resp. eine Pseudo-UN und was ist hier mit Zyklus gemeint.
Danke und Gruss, Peter
Noch zum Vorschlag mit einer Konstante zu arbeiten.
Hier hätte mir vorstellen können, dass ich später alle Monate des ersten Semesters durcharbeite, also nicht nur 02, sondern von 01 bis 06 - deshalb habe ich hier nicht in Betrachte gezogen, mit einer Konstante zu arbeiten.

Anzeige
AW: Manchmal hat auch der Dumme(?) Glück :-)
07.05.2013 14:27:05
Klaus
Hallo Peter,
Luc hat mit der integer-Sache völlig recht. In meinem Code hatte ich deine Deklaration stumpf kopiert, aber nicht überprüft. Auch wenn es funktioniert, sollest du es nochmal ändern - vergleiche auch meinen OT-Kommentar, wenn du in 3 Jahren den Code kopierst um alle Blätter mit "Einkauf" zu finden, dann läuft das mit integer warscheinlich nicht mehr!
UN ist die sogenannte "Ungarische Notation", mit der Variablen benannt werden (sollten). Statt "strLinks" hättest du das ganze auch als "IchHabeMeineKatzeLieb" benennen können, das hätte aber relativ wenig logischen Sinn ergeben. Die UN folgt gewissen Konventionen, die hier im Forum nicht unbedingt jedesmal eingehalten werden (auch nicht von mir!). WÄRE das ganze als integer genutzt, hättest du es "intLinks" nennen sollen. Ich hätte es dann "iLinks" genannt - das ist es was Luc als Verballhornung hinstellt.
Zu der Sache mit den Konstanten wünsche ich mir von Luc nochmal Aufklärung.
Luc, welchen Vorteil hat
Const strLinks As String = "02"
gegenüber dem hier (und von mir immer) genutzten
dim sLinks As String
sLinks = "02"

ich habe gerne alle Dimensionierungen zusammen ganz oben im Makro, und dann einen Bereich in dem ich allen Variablen ihre Werte zuweise. Bereits im Dimensionierungs-Block per CONST einen Wert zuzuweisen erscheint mir schlicht unpraktisch?
Anderes Beispiel: ich schreibe gerne solcherart Code:
Sub Test()
dim wksOld as worksheet
dim lRow as long
dim iCol as integer
[Code]
set wksOld = Sheets("Tabelle1") 'hier den Tabellennamen anpassen
lRow = 5 'mit Zeile 5
iCol = 7 'mit Spalte 7
[Code]
with wksOld.cells(lrow,icol) ...

welchen Vorteil habe ich hier, wenn ich CONST deklariere?
Zu deinem Zyklus: der Code, den du hier vorgestellt hast, ist ziemlich "Spagetti". Die Case-Y Anweisung zum Beispiel funktioniert zwar bestimmt, ist aber sehr wirr und sperrig (und unnötig).
Grüße,
Klaus M.vdT.

Anzeige
Const vs. Variable,...
07.05.2013 14:31:17
EtoPHG
Klaus,
Die Frage ist, was ist eine Variable. Wenn wie der Name schon sagt, etwas variable Werte annehmen kann, dann soll's eine Variable sein. Wenn etwas, wie im vorliegenden Fall konstant durch den Code gleichbleibt dann eine Konstante. Sie sollten noch vor den Variablen-Deklaration liegen und können so bei ev. Neuanforderungen oder Änderungen schnell angepasst werden, ohne grosses Suchen im restlichen Code.
Gruess Hansueli

AW: Const vs. Variable,...
07.05.2013 14:57:12
Klaus
Hallo Hansueli,
dann sollte ich, der Konvention folgend, das ganze etwa so machen (Der Code-Effekt ist hier natürlich Blödsinn):

Sub Test()
'alle Zellen einer gegebenen Tabelle in einer gegeben Zeile einmal anfassen
Const wksOld as worksheets = Sheets("Tabelle1")
Const lRow As Long = 5
Dim iCol As Integer
Dim rBereich As Range
With wksOld
iCol = .Cells(lRow, .Columns.Count).End(xlToLeft).Column
For Each rBereich In .Range(.Cells(lRow, 1), .Cells(lRow, iCol))
rBereich.Select
Next rBereich
End With
End Sub

Ok, das macht sogar Sinn und ist übersichtlicher. Merke ich mir!
Bei der Gelegenheit: ein Worksheet KANN ich gar nicht Const-Deklarieren? Das o.g. Testmakro markiert mir die Zeile rot und erwartet einen "Type Name"? Schade - gerade das hätte für mich Sinn gemacht!
Luc,
ich habe bei dir zwischen den Zeilen rausgeleden dass du dich über "iCol" mokierst und es stattdessen "intCol" benannt werden sollte? Die Wikipedia-Seite zum Thema ist leider relativ schlecht, da finde ich nichtmal wie ein string korrekt heissen soll.
Und auf meiner Lieblings-Nachschlageseite, der von Peter Haserodt, wird sogar
Dim iRow as Long
vorgeschlagen (1)....
hast du mal eine vernünftige Nachleseseite für mich zu diesem Thema?
Grüße,
Klaus M.vdT.
(1) http://www.online-excel.de/excel/singsel_vba.php?f=4

Anzeige
Nachschlagewerke ?
07.05.2013 15:22:58
EtoPHG
Hallo Klaus,
1. Const können nur von Datentyp Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String oder Variant sein. Worksheet ist ein Objekt und per se variabel!
2. Die Ungarische Notation ist auch nicht das Gelbe vom Ei und sehr C-orientiert. Um es mit Lucs Worten zu sagen: Für VB/VBA muss sie ein bisschen verballhornt werden:
Nachschlagwerk "Objekte"
Wikibooks "Visual Basic 6"
Das berühmte "XLAM"
Letztere dürfte für reine Variablen (nicht Objekte!) die Verbreiteste sein!
Gruess Hansueli

AW: Nachschlagewerke ?
07.05.2013 15:42:26
Klaus
Worksheet ist ein Objekt und per se variabel!
Schade. Worksheets sind wohl das, was ich mit Abstand am häufigsten "konstant" definiere ... damit hat das ganze (für mich) keinen Nutzen mehr. Wenn ich eh unterhalb der DIMs einen "hier ändern" Block für die Worksheets brauche, kann ich da auch gleich meine Row- und Col- Variablen mithinschreiben.
Den XLAM Link hab ich mal überflogen, das scheint das richtige zu sein. Danke sehr!
Grüße,
Klaus M.vdT.

Wieso nicht brauchbar! Auch, wenn es ...
07.05.2013 15:53:30
Luc:-?
…ginge, würde ich ein Objekt nicht auf eine Konst (idR auch nicht auf eine GlobalVariable) legen, Klaus;
da reicht doch der ObjektTitel als Konst, es sei denn, du willst InternNamen verwenden, was es kompliziert bzw gar unmöglich machen würde (kann da jetzt nicht drüber nachdenken)!
Gruß Luc :-?

AW: Wieso nicht brauchbar! Auch, wenn es ...
08.05.2013 08:27:54
Klaus
Hallo Luc,
da reicht doch der ObjektTitel als Konst
wie Recht du hast! Sorry, da hatte ich einen akuten Anfall von "Wald vor Bäumen usw".
Grüße,
Klaus M.vdT.

Das Gleiche gilt übrigens auch für ...
07.05.2013 17:58:39
Luc:-?
…Bereichsadressen, Klaus;
außerdem wird die Verwendung von Konstt von MS ausdrücklich empfohlen (ich lege ggf sogar eigene Enumerationen an!), weil das nachträgliche PgmÄnderungen/-Anpassungen sehr vereinfachen kann. Das hat ja wohl auch HU schon (sinngemäß) erwähnt.
Luc :-?

Das kannst du inzwischen beinahe halten ...
07.05.2013 15:48:17
Luc:-?
…wie du willst, Klaus,
und der Wikipedia-Artikel, den ich gelesen habe ist sogar recht gut, weil er auch gleich den Ursprung dessen, was ich Verballhornung nenne, erklärt. Das haben die Windows-Pgmmierer verbockt, nicht die von Office (die hatten sie mal in akademischen Archiven entdeckt), die die UN noch im Sinne des Erfinders benutzt hatten, denn es kommt ja wohl nicht darauf an, im UN-Präfix die Deklaration zu wiederholen, sondern eher, den Verwendungszweck zu vermerken. Darauf können sich die an einem Projekt beteiligten Pgmierer einigen, indem sie vor Beginn der Arbeiten eine sog Namenskonvention festlegen. Die muss ja auch nicht im Detail der UN folgen. PH scheint, wenn überhpt, eher Anhänger eines moderaten bzw Verwendungs­zweck­Präfix zu sein, deshalb das i für Long, denn sowohl Integer als auch Long meinen ja Ganzzahlen (wenn auch m.unterschdl Wertevorrat).
Allerdings gibt's auch Ggmeinungen, die einen sauberen Code (ohne UN) fordern (wohl aus dem gleichen Grund, warum Schlüssel nicht sprechend sein sollten → ggf größerer Änderungsaufwand zur Vermeidung von Ungereimtheiten wie sie Peter hier wunderbar demonstriert hat). ;-)
Ich verwende UN-analoge Präfixe nur manchmal (muss mich ja mit niemandem abstimmen) und dann eher verwendungs­zweckbezogen. Ein schönes Bsp dafür ist mein Präfix is-, das ich sowohl für Boolean- als auch Integer-Variablen (meist aus Enumerationen) einsetze, wenn sie Vergleichen dienen sollen, zB Let isRange = TypeName(Bezug) = "Range", was einen Booleschen Wert ergibt, oder Dim isAnything As msoTristate, was letztlich einem (speziellen) Integer-Wert entspricht.
Im Übrigen soll MS inzwischen von der UN abgerückt sein — war halt doch schon sehr betagt. In den Code-Bspp der VBE-Hilfe findet man das auch nicht! Desweiteren sollte man den Endnutzer aus so etwas heraushalten, also weder ProzedurNamen mit vorangestelltem fnc bzw prc noch Argumente und Parameter derselben derart „ausschmücken“! Das wirst du bei mir auch nicht finden. Nebenbei, die GB-Regel, die manche bei Konstantennamen vereinbart haben, finde ich auch nicht so toll.
Gruß Luc :-?

Ein Minnegesang auf den Blödsinn ;-)
07.05.2013 14:25:18
EtoPHG
Hallo Luc,
Da sind mir und den anderen Antwortern wieder mal die Pferde durchgebrannt.
Ich hab es übersehen (See but don't understand, Copy, Paste, Correct) und Klaus' Empfehlung mit Left(variable1,Len(variable2) = variable2) würde dann wohl bei dem Konstrukt auch 'durchbrennen'.
Deine Anmerkungen in Ehren, glaubst du wirklich jeder hier Mitlesende verstehe auf Anhieb was UN heisst?
Schliesslich sind wir nicht ausgebildete Rocket-Scientists, sondern eher mit Warzenschweinen zu vergleichen ;-)
Der Sänger darf ruhig unhöflich werden :-)
Gruess Hansueli

Danke für die Anmerkung, ...
07.05.2013 16:10:23
Luc:-?
…Hansueli;
am wichtigsten scheint mir beim 2.Link dieser Textteil zu sein …
"Well, you'd have to change the variable name, I guess. But!" I exclaimed, "That then forces you to examine each usage of the variable in your program, to ensure that it is still being used properly."
"Not a bit of it," she riposted. "It 'forces' you to do no such thing. In many cases, the programmers will simply forget — or worse, not bother — to consistently and globally change the variable name, never mind check the usage. And once they do not change the name, the code is lying to you, thus violating the commandment that says: 'Speak truth each one of you with his cubicle neighbor.' This," she shook her head quietly, "is reprehensible. Such is the disinformational evil that must of necessity follow, sooner or later but probably sooner, from the deceptive wartHog style."

Hier geht's wohl in 1.Linie um die verballhornte UN iwS → ganz meine Meinung! ;-)
Gruß Luc :-?

Das hätte ich nicht lesen dürfen!
08.05.2013 08:25:49
Klaus
Hallo Hansueli,
den wartHog Link hätte ich nicht lesen dürfen, ich bin doch so leicht beeinflussbar! In meiner langen und flachen autodidaktischen VBA-Lernkurve habe ich mir mühsamst angewöhnt, die UN zu Nutzen (noch bevor ich wusste dass die UN heisst). "Echte" Programmierer machen das so, dann muss ich das auch so machen! Und mitten in meiner Phase des fanatischen Nacheiferns haust du so einen Link raus! Jetzt glaube ich, dass "echte" Programmierer lange Variablennamen ohne Präfixe nutzen und muss einem neuen Ideal nacheifern!
Damals, als wir noch jünger waren und Q-Basic und GW-Basic programmiert haben, war derjenige mit den kryptischsten Variablennamen der coolste.
Grüße,
Klaus M.vdT.
P.S.: für das gesamte Posting gilt ;-)

Aus bzw vor dieser Zeit resultiert auch ...
08.05.2013 15:53:25
Luc:-?
…die UN (wohl 1972), Klaus;
damals durften die Namen nicht so lang wdn und eine bessere Erkennbarkeit des Gemeinten (bzw wenigstens seines Verwendungszwecks) war gefragt. Das ist natürlich heutzutage nicht mehr erforderlich. Also besser einen Kompromiss zwischen allzulang (hoher Schreibaufwand, aber selbsterklärend) und allzukurz (geringerer SchrbAufwd, aber unverständlich) finden. Echte UN (wird in Foren allerdings kaum propagiert) kann dabei manchmal ein bisschen helfen — aber bitte keine Warzen! ;-)
Gruß Luc :-?

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige