PDA

Vollständige Version anzeigen : Code Umstellung von Zeile auf Spalte


galli
22.09.2016, 08:13
Hallo Profis,

ich komme einfach nicht weiter. Tagelanges googlen und probieren haben nichts gebracht!

Ich habe folgenden Code:

Sub Leerzeilen()

' sortiert das ganze Blatt alphabetisch nach Inhalt von E10
' und fügt Leerzeilen nach Eingabeaufforderung ein

Application.ScreenUpdating = False

If ActiveSheet.ProtectContents = True Then
MsgBox "Bitte Bearbeitungsmodus aktivieren!."
End
Exit Sub
Else
End If

Dim i As Long
Dim tbl As Worksheet
Dim vAnzahl As Variant

Do
vAnzahl = Application.InputBox("Wie viele Leerzeilen sollen eingefügt werden? Bitte eine Ziffer zwischen 1 und 10 eingeben!", " Die Anzahl der Leerzeilen angeben.", Type:=1)
If vAnzahl = False Then Exit Sub
Loop While vAnzahl < 1 Or vAnzahl > 10

Set tbl = Sheets("Material")

For i = 1000 To 11 Step -1
If tbl.Range("E" & i) <> tbl.Range("E" & i - 1) Then
tbl.Range(tbl.Range("E" & i), tbl.Range("E" & i + vAnzahl - 1)).EntireRow.Insert Shift:=xlDown

End If
Next i

Application.ScreenUpdating = True

End Sub

Dieser Code fügt über in eine Inputbox einzutragende Anzahl Leerzeilen ein.
Ausschlaggebend dafür ist Spalte E ab Zeile 10. Dort stehen Wörter untereinander die zuvor alphabetisch sortiert wurden. Der Code sucht nun nach sich ändernden Anfangsbuchstaben und fügt die Leerzeilen dazwischen.

Ich brauche nun diesen Code so umgestellt, dass nach dem gleichen Prinzip Leerspalten in einer anderen Tabelle eingefügt werden. Die Anzahl der Leerspalten soll über die gleiche Inputbox abgefragt werden.
D.h., wenn 2 Zeilen eingefügt werden, sollen in der anderen Tabelle auch 2 Spalten eingefügt werden.
Ausschlaggebend hierfür sind die Inhalte in Zeile 2 ab Spalte E.

Bisher habe ich folgenden Ausschnitt zurechtgebastelt: (der Rest ist wie im ersten Code)
Set tbl = Sheets("Baugruppen")

For i = 1000 To 11 Step -1
If tbl.Range("E" & b) <> tbl.Range("E" & b - 1) Then
tbl.Range(tbl.Range("E" & b), tbl.Range("E" & b + vAnzahl - 1)).EntireColumn.Insert Shift:=xlLeftToRight

Der Code kommt bei
If tbl.Range("E" & b) <> tbl.Range("E" & b - 1) Then
ins Stocken.
Ich habe keine Ahnung was da falsch ist. Ob der ausschlaggebende Bereich (Zeile 2 ab Spalte E) richtig ist, weiß ich auch nicht!

Ich hoffe, ihr könnt mir helfen.

Gruß galli

Benutzername:
22.09.2016, 08:41
Tach galli,

Ohne genau zu verstehen was Du machst, aber du nutzt in der Schleife die Variable "i", in der Adressierung aber eine Variable "b".

Ist das gewollt und "b" kommt noch weiter oben im Code vor?

Falls nicht eh schon geschehen würd ich immer Option Explicit nutzen, dann würden solche Fehler immer gleich bemerkt werden.

Gruss,
Stephan

galli
22.09.2016, 09:09
Hallo Stephan,

das "b" im unteren Code soll eigentlich Zeile 2 definieren.
Im oberen Code denke ich, steht das "i" dafür, dass ab Zeile 9 "gesucht" wird?!
Im Übrigen sind meine Excel Kenntnisse sehr begrenzt. Alles was ich habe ist irgendwo geklaut! ;-)

Gruß
galli

Benutzername:
22.09.2016, 10:10
Servus Galli,

jetzt verstehe ich langsam ;)

Also, i und b haben in dem Fall nichts mit den Spaltennamen zu tun.

Diese werden als Variablen genutzt. Der Name dieser Variablen ist beliebig wählbar, könnte also auch "zeilenIndex" oder Ähnliches heissen. Aus Faulheit nutzt man für Schleifen meist i,j,k usw.
Diese Variable bekommt dann einen Wert zugewiesen. Hier durch die Schleife Werte zwischen 1000 und 11. Aber das alles zu erläutern ginge hier zu weit. Details zu Variablen und co findest Du in den ganzen schönen Tutorials im Internet ;)

Ich habe mal Deinen vorhandenen Code ein bisschen angepasst damit er Spalten statt Zeilen einfügen sollte.
Du musst noch den Namen der Zieltabelle (HIER_UMBENENNEN) ändern.
Dann mal sehen ob es das ist was du haben wolltest.


Sub Leerspalten()

' und fügt Leerzeilen nach Eingabeaufforderung ein

Application.ScreenUpdating = False

If ActiveSheet.ProtectContents = True Then
MsgBox "Bitte Bearbeitungsmodus aktivieren!."
End
Exit Sub
Else
End If

Dim i As Long
Dim tbl As Worksheet
Dim vAnzahl As Variant

Do
vAnzahl = Application.InputBox("Wie viele Leerspalten sollen eingefügt werden? Bitte eine Ziffer zwischen 1 und 10 eingeben!", " Die Anzahl der Leerzeilen angeben.", Type:=1)
If vAnzahl = False Then Exit Sub
Loop While vAnzahl < 1 Or vAnzahl > 10

Set tbl = Sheets("HIER_UMBENENNEN")

For i = 1000 To 5 Step -1
If (tbl.Cells(2, i).Value <> tbl.Cells(2, i - 1).Value) Then

tbl.Range(tbl.Cells(2, i), Cells(2, i + vAnzahl - 1)).EntireColumn.Insert Shift:=xlRight

End If
Next i

Application.ScreenUpdating = True

End Sub

galli
22.09.2016, 13:26
Hallo Stephan,

erst einmal vielen Dank!
Habe den Code getestet. Soweit funktioniert er wunderbar.
Leider werden dabei aber auch gleich vor der eigentlich ersten Spalte Leerspalten eingefügt.
Lässt sich das noch ändern? in der Tabelle sollen in Spalte E die ersten Daten stehen!

Gruß
galli

Benutzername:
22.09.2016, 13:36
Tach Galli,

klaro, mach einfach aus der Zeile:
For i = 1000 To 5 Step -1

die Zeile:
For i = 1000 To 6 Step -1

Dann wird erst ab der 6ten Spalte (F) die Leerspaltenlogik genutzt.

Gruss,
Stephan

galli
22.09.2016, 18:36
Hallo Stephan,

jetzt funktioniert es super. Danke!
Da taucht aber gleich eine neue Frage auf:
Gibt es eine Möglichkeit die in den vorhandenen Spalten enthaltenen Formel in die neuen Spalten gleich mit einzufügen?
Bsp.:

in E4 steht =INDEX(Material!$A:$A;SPALTE(J8))
in E5 steht =INDEX(Material!$B:$B;SPALTE(J9))
in E6 steht =INDEX(Material!$E:$E;SPALTE(J9))

in E4 steht =INDEX(Material!$A:$A;SPALTE(K8))
in E5 steht =INDEX(Material!$B:$B;SPALTE(K9))
in E6 steht =INDEX(Material!$E:$E;SPALTE(K9))

usw.

Diese Formeln müssen letzendlich in allen Spalten ( ca. 1000) stehen.
Vielleicht hast du ja auch dafür eine Lösung parat?!

Gruß
galli

Benutzername:
22.09.2016, 19:33
Tach Galli,

wenn ich dich richtig verstehe sollen also die links vorhandenen Spalten in die neu erzeugten Spalten kopiert werden?

Das sollte hoffentlich/vielleicht/eventuell einfach gehen wenn Deine Formeln schon entsprechend korrekt relativ und absolut addressiert sind.

Ersetzen mal versuchsweise folgendes:

tbl.Range(tbl.Cells(2, i), Cells(2, i + vAnzahl - 1)).EntireColumn.Insert Shift:=xlRight



durch das hier:

'kopiere spalte i-1
Columns(i - 1).Copy

'paste
tbl.Range(Columns(i), Columns(i + vAnzahl - 1)).Insert Shift:=xlRight


Aber prüfe das auf jeden Fall nochmals händisch.

Gruss,
Stephan

galli
23.09.2016, 08:59
Hallo Stephan,

ich habe das mal getestet. Da kam aber nur wirres Zeug bei raus.
Dabei ist mir aber aufgefallen, dass die Formeln ab Spalte E nach dem Spalteneinfügen geändert werden!? Wenn 2 Spalten eingefügt werden steht vorher in E4 INDEX(Material!$A:$A;SPALTE(J8)), danach INDEX(Material!$A:$A;SPALTE(L8)). Also genau 2 mehr. Je mehr Spalten eingefügt werden, um so mehr steigt der Wert in der Formel!

Ich füge mal eine Beispieldatei an, so ist es vielleicht einfacher!

Gruß
galli

Benutzername:
23.09.2016, 17:58
Servus Galli,

ich habe mir mal deine Beispieldatei angesehen.

So wie ich das sehe hast du jede Menge relative Verweise in deinen Formeln.
Dieser werden sich nach Einfügen neuer Spalten/Zeilen entsprechend weiter verschieben.

Ist meist auch ein erwünschtes Verhalten für dynamische Formeln.

Wenn Du die Adressen fix halten möchtest kannst Du diese in einer Formel fixieren mit einem $ (z.B. $A$2). Wobei ich nicht weiss ob das hier zweckmässig wäre.

Ich muss aber auch gestehen ich verstehe spontan die Formeln bzw. deren Funktion nicht.

Allerdings kenne und nutze ich dank VBA auch nur die rudimentärsten simplen Formeln, sobald es nur bisschen komplexer wird mach ich alles mit VBA.

Gruss,
Stephan

galli
23.09.2016, 23:12
Hallo Stephan,

trotzdem vielen Dank. Für das Problem habe ich eine Lösung gefunden. Jeder Excel Profi würde sich die Haare raufen, aber es funktioniert!
Ich lasse die Formeln im nachhinein per VBA in die Zellen eintragen und weiter kopieren. Wie gesagt, nicht schön, aber geht!

Vielleicht kannst du mir aber, bevor ich in meinen wohl verdienten Urlaub entschwinde bei einem anderen Problem weiterhelfen.
Es geht wieder um die Umstellung eines Codes von senkrecht nach waagerecht.
Ich habe folgenden Code:
Range("A10", Range("E65536").End(xlUp).Offset(0, 5)).Select
Selection.Sort Key1:=Range("E10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Dieser sortiert mir alle Einträge in Spalte E ab Zeile 10 alphabetisch. Dabei werden die jeweiligen Zeileninhalte in den anderen Spalten mit verschoben.

Ich hätte nun gerne einen Code, der mir alle Einträge in Zeile 6 ab Spalte E alphabetisch sortiert und dabei auch die jeweiligen Spalteninhalte mit verschiebt.

Danach hast du für mindestens 2 Wochen Ruhe vor mir. Versprochen! ;)

Gruß
Galli

galli
24.09.2016, 08:51
Nachtrag:

Ich habe das natürlich bereits über die in Excel enthaltene Sortierfunktion versucht.
Außerdem muss ich noch erwähnen, dass in Zeile 6 auch ausgeblendete Nullwerte stehen.
Nach dem Sortieren standen diese Spalten immer an erster Stelle. Ich hätte nun gerne, dass diese hinten und alles Andere ab E6!

Benutzername:
24.09.2016, 09:42
Servus Galli,

super das du es selbst hinbekommen hast =)
Anscheinend hast genau die richtige Methode gewählt.

Ich hab auch gerade Urlaub und hab wohl Sehnsucht nach Arbeit. Wegen Excel und so ;)

So langsam verstehe ich auch was genau Du mit der Exceldatei machen willst. Hier ein spontaner und keinesfalls zu Ende gedachter Geistesblitz, wäre es nicht einfacher gewesen auch die Baugruppentabelle in Spalten zu ordnen, also die Baugruppen jeweils in die Spalten hauen?
Dann wären die Materialienzeilen in beiden Tabellen identisch?
Wie gesagt nur eine spontane Idee, kann auch Unsinn sein ;)

Wegen Sortierung habe ich mal dein Beispiel probiert, da will nicht mal die genannte Standardexcelsortierfunktion.

Verstehe ich das richtig, die Zahleneinträge in die untere Tabelle im Sheet Baugruppen macht Ihr von Hand, oder? Damit definiert Ihr quasi welche und wie viel Bauteille in die entsprechenden Schränke müssen?

Kann man genau definieren wie gross die Tabelle am Schluss sein wird?
Dann könnte man diesen Bereich per Makro sortieren.
Die Leerzeile zwischen Schranktyp 5 und 6, ist das Absicht? Macht sich in einer Tabelle beim Sortieren normalerweise nicht so gut.

Gruss,
Stephan

galli
24.09.2016, 10:18
Hallo Stephan,

dass ich im Urlaub Sehnsucht nach Arbeit habe, müsste mir auch mal passieren! :mad:
Aber gut für mich! ;)

Eine gleichmäßige Aufteilung in beiden Tabellen ist ungünstig, eigentlich sogar nicht machbar. Das was du siehst ist nur die Spitze des Eisberges. Die Datei ist um einiges umfangreicher!

Verstehe ich das richtig, die Zahleneinträge in die untere Tabelle im Sheet Baugruppen macht Ihr von Hand, oder? Damit definiert Ihr quasi welche und wie viel Bauteille in die entsprechenden Schränke müssen?
Damit hast du vollkommen recht!

Die Tabelle umfasst momentan in etwa 1100 Zeilen / Spalten, kann aber noch wachsen!

Die Leerzeilen dazwischen dienen eigentlich nur der Übersicht. Wenn da nachher um die 200 Baugruppen drin stehen verliert man schnell den Überblick. :upps:

Zusammenfassend gesagt soll eigentlich folgendes geschehen:
In Tabelle Material wird auch das Material mit allem Drumherum per Hand eingetragen. Im Normalfall ist dann nicht alles gleich sortiert wie teilweise in der Beispieldatei.
Das Material wird per Formel in Tabelle Baugruppen übertragen. Dort wird die Menge für die Baugruppen eingetragen.
Im Laufe der Zeit kommt neues Material dazu. Dann soll es möglich sein von Tabelle Material aus einen Sortiervorgang nach Lieferanten zu starten und dazwischen eine Anzahl von Leerzeilen zu lassen. Die Anzahl wird über die InputBox abgefragt.
Das funktioniert ja auch zur Zeit.
Jetzt muss aber das Gleiche in Tabelle Baugruppen geschehen, aber so, dass die Zahlen auch in der entsprechenden Spalte bleiben!
Genau das bekomme ich nicht hin!

Gruß
galli

Benutzername:
24.09.2016, 11:10
Tach Galli,

oki, nun hab ich die Formeln auch halbwegs verstanden und auch der Grund das die Spalten und Zeilen der beiden Tabellen immer sykron bleiben müssen.

Du wirst da aber in Teufels Küche kommen sobald die Tabellen aus welchen Gründen auch immer nicht mehr zusammenpassen (gelöschte Zeile in Materialien, Spalte nicht nachgezogen, usw.).

Es scheint so als wäre das Problem durch die Indexformel in den Spaltenköpfen und den neuen Spalten entstanden. Ich habe die Formel nun mal angepasst so das diese sich immer auf sich selbst bezieht und dann nochmal 5 addiert (damit man eben auf Zeile 10 für das erste Material im Sheet Material bekommt).

Damit könnte/sollte die Copy + Pastegeschichte die ich mal geschrieben hatte wieder funktionieren.

Beispiel für
E4:
=INDEX(Material!$A:$A;SPALTE(E1)+5)

E5:
=INDEX(Material!$B:$B;SPALTE(E1)+5)

E6:
=INDEX(Material!$E:$E;SPALTE(E1)+5)

Das könntest Du dann nach rechts ziehen.

Trotzdem tut mir die Lösung innerlich schon ein bisschen weh - alleine wegen der Fehleranfälligkeit ;)

Wegen der Sortierung, hattest Du dann diese Spaltensortierung gemeint, oder wolltest Du zusätzlich noch die Zeilen Sortieren?

Gruss,
Stephan

galli
24.09.2016, 15:40
Hi Stephan,

wie gesagt, das mit den Formeln habe ich so gelöst, dass sie am Ende der Prozedur per VBA neu eingetragen werden. So ist sichergestellt, dass sie immer korrekt sind. Bei deiner Variante müsste die +5 immer noch manuell eingegeben werden. Außerdem ist die Zahl abhängig von der in die Inputbox einzugebenden Zahl und ist damit variabel!

Zum Sortieren:
In Tabelle Material funktioniert es. Ich hoffe ich drücke mich richtig aus, dort werden die Zeilen sortiert, und zwar nach Inhalt von Spalte E.
In Tabelle Baugruppen sollen die Spalten nach Inhalt von Zeile 6 sortiert werden.
Ich war in der Zwischenzeit auch nicht untätig und füge mal meine überarbeitete Tabelle an.
Da funktioniert auch schon einiges, nur die Zuordnung der Spalteninhalte in Tabelle Baugruppen stimmt nach dem Sortieren nicht.
Zur besseren Übersicht stehen im Originalzustand in Zeile 10 die Spalten-Buchstaben und in den Zeilen 16-18 der Originalinhalt aus den Zeilen 4-6. Dieser Inhalt sollte nach dem Sortieren wieder übereinstimmen. Macht es aber nicht! :-(
Das Makro wird in Tabelle Material über den Button Sortieren gestartet.
Im Modul sind auch Erläuterungen zu den einzelnen Code Abschnitten.

Ich hoffe es hilft weiter!

Gruß
galli

Benutzername:
24.09.2016, 19:51
Tach galli,

oki, ich verstehe.

Ahmmm.... joa...

In der derzeitigen Form wird das so nicht klappen.
Tatsächlich werden nur die Spaltenköpfe der Tabelle sortiert dargestellt, und das auch nur durch die Formeln die einfach fix ab der 10. Zeile in das Materialsheet sehen.

Die eingeflegten Zeilen unterhalb erfahren keinerlei Sortierung.
Deshalb passen die Zahlen die unten eingegeben wurden auch niicht mehr mit den Spaltenköpfen überein sobald die Reihenfolge im Materialsheet geändert wird.

So spontan bin ich da ein bisschen ratlos. :(

Gruss,
Stephan

galli
25.09.2016, 10:05
Moin Stephan,

das hört sich ja gar nicht gut an! :-(
Sollte dir noch was einfallen, kannst du dich gerne noch mal melden!

Danke und Gruß
galli

Benutzername:
26.09.2016, 07:10
Moin galli,

ich dachte Du bist schon im Urlaub?
Arbeitest doch noch weiter, Steber! ;)

Also mir ist heute Nacht (*hust*) doch noch eine Idee gekommen.

Die von Euch genutze Material ID, ist diese eindeutig?
Ansonsten, wäre es ein Problem eine zusätzliche Zeile in das Sheet Baugruppe zu machen und dort die Material ID reinzuhauen?

Zudem würde ich keine Leerzeilen/Spalten in keiner der Tabellen nutzen.
Stattdessen würde ich da lieber einfach per Makro bei Änderung des Anbieters den Hintergrund der Zellen leicht einfärben.

Gruss,
Stephan

galli
26.09.2016, 09:16
Hallo Stephan,

ich kann halt doch nicht über meinen Schatten springen! Aber ab heute Mittag bin ich dann wirklich weg.

Schön, dass du noch eine Idee hast!
Wenn du mit Material ID die Artikelnummer meinst, dann ja.
Aber danach sollte möglichst nicht sortiert werden. Diese mit einzufügen wäre aber nicht das Problem.
Das mit den Leerzeilen/Spalten ist nicht so einfach.
1. Wenn ich diese entferne, müsste ich um die 100 Makros überprüfen und ggf. überarbeiten. :-(
2. Die Leerzeilen/Spalten füge ich gerade deshalb ein, damit diese Sortierei nicht all zu oft gemacht werden muss. So habe ich immer einige Möglichkeiten noch etwas nachzutragen. In der richtigen Datei dauert der Sortier-und Einfügevorgang ohne Tabelle Baugruppen jetzt schon fast 1 Minute!!! Es müsen dabei per VBA in verschiedenen anderen Tabellen auch noch einige Änderungen vorgenommen werden. Wie gesagt, die eigentliche Datei ist um einiges umfangreicher!

Also Material ID einfügen ist kein Problem. Sie könnte in Zeile 3 oder 7 stehen.

Und dann???

Gruß
galli

Benutzername:
26.09.2016, 10:24
Moin Galli,

Sortieren eine Minute :boah:
Hört sich nach einem klassischen Frankenstein-VBA-Excel-Monster an :grins:
Mein Beileid...

Ich habe nun mal das Makro angepasst.
Er geht durch die Materialien und prüft ob die Material Id bereits im Baugruppen Sheet vorhanden ist.

Falls ja:
-> verschiebe Spalte auf neue Position (mit händisch eingegebenen Daten)

Falls nein:
-> Erzeuge neue Spalte und kopiere Daten rein.

Scheint zu klappen, ich hatte zumindest mal ein paar Anwendungsfälle getestet.

Nichtsdestotrotz natürlich nochmal bitte gegenprüfen bevor dein Excelmonster zerschiesst ;)

Gruss und schönen Urlaub,
Stephan

galli
26.09.2016, 12:14
Gallo Stephan,

bin jetzt sehr in Eile!
Habe den Code kurz getestet. Scheint wirklich zu funktionieren!
Keine Ahnung was du da hingezaubert hast und wie das funktioniert. Das sehe ich mir nach dem Urlaub mal genauer an!

Erst einmal vielen Dank für deine Hilfe!!! :-)
Wenn ich darf werde ich dich ggf. noch mal per PN oder Mail kontaktieren?!
Ich lasse den Thread mal so lange noch offen.

Gruß und Dank
galli