Per Makro auf ein Datum springen

[Wastl]

Hi,

du hast mitten auf der Seite einen Button erzeugt, warum?

Das Textfeld oben links im Eck bei A1 habe ich in einen Button umfunktioniert.
Mit Klick auf diesen Button macht das los

Mein Code steht in Tabelle1, Doppelklick darauf im VBAEditor, und du siehst ihn.
du hast diesen Code kopiert und in ein Modul eingefügt.
warum?

Nur wie ich ein "smartes" Einfügen hinbekomme ist mir noch ein Rätsel.

ich füge 3 Spalten ein an Spalte G, dadurch rutsch das rechte weiter nach rechts
Ich hole aus dem Template nur die Texte.
Ich kopiere nix.
Da du die ganze Zeile formatiert hast als 'über Bereich zentrieren' muss ich mich darum nimmer kümmern.
Sobald ich die Text rüberhole machen die automatisch was sie sollen.
Dann färbe ich nur noch rot und mach den Rahmen drumrum

Kopieren brauche ich nur, wenn ich Zellinhalte in einer anderen Anwendung benötige, zb Word, Poweroint, notepad++
Innerhalb Excel so gut wie nie
Zuhause habe ich Excel 2013 da hat jede Mappe eine eigene Symbollieiste, aber ohne Tricks ist das immer die selbe Anwendung.
Kopieren muss nur, wenn ich mehrere Excel-Instanzen gleichzeitig offen habe. Dazu muss ich aber Excel mit Startparametern zwingen.

[Wastl]

Hi,

habe fertig

Code:

Private Sub CommandButton1_Click()
'erstellt von Wastl am 15.1.2018 + geändert am 17.1.
'Beispiel_Ausgangslage zeigt die technischen Änderungen eines Bauteils auf. Es ist in der Tabelle erkennbar, wie sich die Teilenummer sowie der Preis eines Bauteils über die Zeit ändert. Nun soll über ein Makro eine neue technische Änderung hinzugefügt werden. Als Beispiel habe ich hier eine zusätzliche Änderung zum 5.12.2017 eingetragen.
'Die Datei Beispiel_Ziel zeigt das optimale Resultat auf.

' Wastl: am Besten auf die erste Anweisung mit F9 einen Haltepunkt setzen
' mit der selben Taste geht der wieder weg.
' dann den Knopf drücken. Makro läuft bis zum Haltepunkt und stoppt dann.
' Mit F8 machste im Einzelschritt weiter.
' lass dir das Localfenster anzeigen aus Menü Ansicht
' Trage im Überwachungsfenster ein:
' VX(2, langI-3)
' hier steht das Datum aus der Schleife in der Tabelle
' Bei Fragen bitte melden

Dim Aenderung As Date
Dim VX As Variant
Dim lngI As Long
Dim VXrow, VXcol

' Tabelle1 in Arbeitspeicher laden
VX = Cells(3, 1).CurrentRegion
' Anzahl Reihen des Datenfelds VX
VXrow = UBound(VX, 1)
' Anzahl Spalten
VXcol = UBound(VX, 2)

On Error GoTo Fähler
Aenderung = InputBox("Bitte Datum eingeben", "Datum", Date)
' Schleife
On Error GoTo 0

For lngI = VXcol - 2 To 4 Step -3
   
   If Aenderung > VX(2, lngI - 3) And lngI > 4 Then
       'laufe von rechts in Zeile 2 durch und vergleiche das Datum mit Änderung
       ' 2 Bedingungen, ok?
   Else
   'Tu was
   Tabelle1.Range(Cells(1, lngI), Cells(VXrow, lngI + 2)).Select
   Selection.Insert Shift:=xlToRight
   Tabelle1.Cells(1, lngI).FormulaR1C1 = Tabelle2.Range("A1")
   Tabelle1.Cells(2, lngI).FormulaR1C1 = Aenderung
   Tabelle1.Cells(3, lngI).FormulaR1C1 = Tabelle2.Range("A3")
   Tabelle1.Cells(3, lngI + 1).FormulaR1C1 = Tabelle2.Range("B3")
   Tabelle1.Cells(3, lngI + 2).FormulaR1C1 = Tabelle2.Range("C3")
   'Bunt (Rot)
   With Tabelle1.Range(Cells(1, lngI), Cells(3, lngI + 2)).Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .Color = 255
       .TintAndShade = 0
       .PatternTintAndShade = 0
   End With
   'Rahmen
   With Tabelle1.Range(Cells(1, lngI), Cells(VXrow, lngI + 2))
   .Borders(xlDiagonalDown).LineStyle = xlNone
   .Borders(xlDiagonalUp).LineStyle = xlNone
   .Borders(xlEdgeLeft).LineStyle = xlNone
   .Borders(xlEdgeTop).LineStyle = xlNone
   .Borders(xlEdgeBottom).LineStyle = xlNone
   .Borders(xlEdgeRight).LineStyle = xlNone
   .Borders(xlInsideVertical).LineStyle = xlNone
   .Borders(xlInsideHorizontal).LineStyle = xlNone
       With .Borders(xlEdgeLeft)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           .TintAndShade = 0
           .Weight = xlMedium
       End With
       With .Borders(xlEdgeTop)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           .TintAndShade = 0
           .Weight = xlMedium
       End With
       With .Borders(xlEdgeBottom)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           .TintAndShade = 0
           .Weight = xlMedium
       End With
       With .Borders(xlEdgeRight)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           .TintAndShade = 0
           .Weight = xlMedium
       End With
   End With
' Format Schrift=weiß und über Bereich zentriert
   With Tabelle1.Range(Cells(1, lngI), Cells(2, lngI + 2))
       .HorizontalAlignment = xlCenterAcrossSelection
       .VerticalAlignment = xlCenter
   With .Font
       .ThemeColor = xlThemeColorDark1
   End With
   End With

   Tabelle1.Range("G1").Select
   ' wenn bis hier gekommen ist restliche Schleife Blödsinn, also raus, damit Sprungmarke nur von Error angelaufen wird
   Exit Sub
   
   End If
Next lngI
' Sprungmarke Fehlerbehandlung
Fähler:
MsgBox "kein Datum eingegeben," & vbCrLf & "mögliche Trennzeichen:" & vbCrLf & "Punkt" & vbCrLf & "Strich" & vbCrLf & "Schrägstrich" & vbCrLf & "Leerzeichen"
End Sub

[ExcelNeuling99]

Hallo Wastl,

das ist ja einfach faszinierend, dass das funktioniert:)

Ich dachte ich bekomme deinen Code auf mein Arbeitsdokument angepasst, aber leider klappt es einfach nicht:(
Vielleicht findest du nochmal kurz Zeit um das anzuschauen? (Ich bin im Bereich VBA noch ein blutiger Anfänger).

Ich habe meine Arbeitsdatei hochgeladen. Ich habe 4 Buttons erstellt (Aktion 1, Aktion 2, ...). Mein Plan ist mit Hilfe des Aktion 1-Buttons die Spalten des Tabellenblatts Template_Aktion1 einzufügen. Mit dem Aktion 2-Button die Spalten des Tabellenblatts Template_Aktion2 usw.
Bisher habe ich deinen Code nur in das erste Modul eingefügt.

In dem Arbeitsdokument steht das Datum in Zeile 3 (bekomme das aber im Code nicht angepasst:(.

Ich hole aus dem Template nur die Texte.

Ich kopiere nix.

Macht es nicht Sinn in diesem Fall die Spalten zu kopieren? Der Grund ist, dass ich in den einzelnen Felder Formeln eingefügt habe, die auch übernommen werden sollen. Zusätzlich könnte ich dann auch später noch Anpassungen (Farbe, Formeln etc.) vornehmen (in den Tabellenblättern Template_*) ohne den Code anfassen zu müssen.

Tausend Dank und sorry dass ich es ab jetzt nicht selbst hinbekommen habe. Nur in den letzten 2.5 Stunden bin ich nicht weitergekommen.

[Wastl]

*Push*

[schauan]

Hallöchen,

erst mal ohne weiter einzusteigen ein Hinweis zum ersten Fehler:

' Tabelle1 in Arbeitspeicher laden
VX = Cells(3, 1).CurrentRegion

Da in Zeile 3, 4 und 5 nichts steht, erzeugst Du damit ein leeres Array und in Folge einen Fehler. 4Lösungsansätze - welcher bei Dir funktioniert, musst Du schauen:

'1. etwas in A4 eintragen

'2. CurrentRegion ab Zeile 5
VX = Cells(5, 1).CurrentRegion

'3. UsedRange
VX = ActiveSheet.UsedRange

'4. Range genau angeben
VX = Range("A3:G25")

[Wastl]

Hallo schauan,

Vielen Dank für diesen Hinweis, an den TE

So sehe ich sein Problem:

Der TE hat eine Beispieldatei gebastelt, was eine stark vereinfachte Form seines Wunsches darstellt.
Auf  dieses Beispiel habe ich was gestrickt, was auch perfekt Funktioniert.

Nun hat der TE in Betrag 13 eine viel umfangreichere Datei hochgeladen, die fast gar nichts mehr mit dem Beispiel zu tun hat, und sagt er bekommt meinen Code da nicht angepasst.

Und zu guter Letzt hat mich mein Arbeitgeber gerade so mit Arbeit zugedeckt, dass ich hoffe, am Montag mal wieder im Forum aktiv guggen zu können.
Ich kann also ExcelNeuliung99 aktuell nicht helfen und hoffe, dass sich jemand anderes findet, der früher Zeit hat.

erst mal ohne weiter einzusteigen ein Hinweis zum ersten Fehler:

Darum Danke schauan, du hast zu erst gewackelt und damit gewonnen

Hallo Excelneuling99

Macht es nicht Sinn in diesem Fall die Spalten zu kopieren? Der Grund ist, dass ich in den einzelnen Felder Formeln eingefügt habe, die auch übernommen werden sollen. Zusätzlich könnte ich dann auch später noch Anpassungen (Farbe, Formeln etc.) vornehmen (in den Tabellenblättern Template_*) ohne den Code anfassen zu müssen.

Nein, ich bin kein Freund von kopieren, weil das
a... langsam ist und es andere bessere Lösungen gibt.
Wenn es mir gelingt, Texte aus einem Tabellenblatt in ein anderes Tabellenblatt zu schreiben ohne dass ich kopiere, dann sollte mir das mit Formeln auch gelingen, da sie ja eignetlich auch nix anderes Sind als Texte.

Wofür Excel wahnsinnig Zeit braucht, sind schreibvorgänge.
Beim kopieren und einfügen, muss wohl geschrieben werden, oda?

...ohne den Code anfassen zu müssen.

Du hast mich per PN nach einem Buch gefragt, mit dem du lernen könntest.
Wenn du lernen willst, lerne Code anzufassen.
Am meisten lernt man mit try and errror, (Versuch und Irrtum)
Also keine Hemmungen.

Nur noch 1 extrem wichtigen Satz: Arbeite, vor allem beim Testen, immer mit einer Kopie.
Und bevor du Code ausprobierst, Speichere die Datei, mit der du gerade spielst.
Es ist kein Problem Excel per VBA in eine Endlosschleife zu schicken. Wenn dann nur noch hilft, Excel im Taskmanager abzuschießen, und dann nicht vor dem Makrostart gespeichert hast :22:

[ExcelNeuling99]

Hallo schauan,
hallo Wastl,

danke für die Hinweise.

Den ersten Fehler konnte ich mit folgendem Hinweis lösen:

'2. CurrentRegion ab Zeile 5
VX = Cells(5, 1).CurrentRegion

Auch Vorschlag 1 hätte funktioniert, jedoch mag ich ungern Inhalt in A4 eintragen müssen.

Was jetzt auch geklappt hat: Das Datum von der Datumseingabe wird korrekterweise in die dritte Zeile geschrieben.

Was ich aber einfach nicht hinbekomme: Nun müssen die Datumsangaben in Zeile 3 miteinander verglichen werden und die Spalten dann entsprechend eingefügt werden.

Ich hänge an diesem Bereich. Ich steige nicht ganz dahinter und auch all meine Versuche führen mich nicht weiter.

For lngI = VXcol - 2 To 4 Step -3
    
    If Datumseingabe > VX(2, lngI - 3) And lngI > 4 Then
        'laufe von rechts in Zeile 2 durch und vergleiche das Datum mit Änderung
        ' 2 Bedingungen, ok?

Nein, ich bin kein Freund von kopieren, weil das
a... langsam ist und es andere bessere Lösungen gibt.
Wenn es mir gelingt, Texte aus einem Tabellenblatt in ein anderes Tabellenblatt zu schreiben ohne dass ich kopiere, dann sollte mir das mit Formeln auch gelingen, da sie ja eignetlich auch nix anderes Sind als Texte.

Okay, das verstehe ich. Wäre auch praktisch, wenn einfach die Textinhalte / Formeln übernommen werden. Nur gibt es einen Weg, dass auch gleichzeitig die Formatierung (Farbgebung, Rahmen etc.) übernommen wird und nicht manuell "programmiert" wird? Warum ich mir darüber Gedanken mache ich folgendes: Falls die Tabelle nämlich später erweitert wird (z.B. eine neue Zeile), dann müsste hierfür der Code angepasst werden. Es wäre natürlich elegant, wenn man dafür nur noch die Tabellenblätter "Template" anpassen müsste, da das schließlich (mehr oder weniger) jeder kann.

Vielen Dank für jeden Tip!

[Wastl]

Guten Morgen,

Was ich aber einfach nicht hinbekomme: Nun müssen die Datumsangaben in Zeile 3 miteinander verglichen werden und die Spalten dann entsprechend eingefügt werden.
Ich hänge an diesem Bereich. Ich steige nicht ganz dahinter und auch all meine Versuche führen mich nicht weiter.

Das ist eines der springenden Punkte, die mich an der ganzen Angelegenheit stört.
Du hast eine - von deiner Realität völlig abweichenden - einfache Beispieldatei gestrickt.
Und genau auf diese Beispieldatei habe ich meinen Code geschrieben. Zur Erinnerung:
Diese Beispieldatei hatte immer 3 Spalten pro Eintrag, und das Datum stand immer in der linken Spalte von den 3en, wenn es auch durch die Formatierung so aussieht, als stände sie in der Mitte.

Bei der aktuellen Datei Arbeitsdokument_3.xlsm ist die Spaltenanzahl variabel.
Bei der Beispieldatei war der Rest leer, außer den von dir gefüllten Spalten.
Wenn ich mir aktuell den Used.Range (UR) angschaue, sagt er mir: UR Reihen = 25, UR Spalten 841
Und wenn ich die Datei mit meinen alten Augen anschaue, dann sind die Spalten mit Inhalt bis Spalte Z gefüllt und der Rest ist irgendwie "dackelhaft" schraffiert.

In der Beispieldatei hatte ich den Current.region ausgelesen und die Anzahl der Spalten der Variablen vxCol zugewiesen.

Code:

For lngI = VXcol - 2 To 4 Step -3

Mein Code hat dann ganz rechts angefangen, dann immer nur 3 Spalten nach vorne hüpfen und den dort gefundenen Datumswert mit dem eingegebenen vergleichen.

Sag mal in Worten, was der Code heute tun soll.
Aktuell ist vxcol =26, 
Beginn hat nur 2 Spalten, das Datum steht in Y3
Fall1 hat 3 Spalten, das Datum steht in V3
Fall2 hat 3 Spalten, das Datum steht in S3
Fall3 hat 7 Spalten, das Datum steht in L3

Mit Aktion1 sollen 7 Spalten eingefügt werden, mit Aktion 2 5 Spalten, mit den Aktionen 3 und 4 jeweils 3 Spalten.
Das ist die Situation. Nun muss da ein Code generiert werden.

###

Ich glaube, du hast den Sinn nicht verstanden.
Ich habe vor Jahren mal für mich den Satz geprägt: Wenn ich bei einer Tätigkeit mehr als 10x den selben Schritt machen muss, beginne ich zu überlegen, ob es nicht mit ein paar Codezeilen besser, schneller zu tun geht.

Makros sind meiner Meinung nach dazu du, ständig sich wiederholende einfache Schritte abzuarbeiten.
Wenn dein Arbeitsdokument irgendwann einmal fertig gestaltet sein sollte, dann, und wirklich erst dann, wenn du bestimmte Schritte ständig - entnervend - wiederholen musst um damit zu arbeiten, also wenn sich das Layout nicht mehr ändert, dann erst, beginne mit VBA.

Und wenn sich dann an dem Layout was kleines ändert, kann / muss man, dann erst, den schon vorhandenen Code anpassen. 

Und vielleicht hast du dir bis dahin schon so viele Kenntnisse erworben, - durch die Fragen anderer hier im Forum und dabei selbst nach Lösungen suchend - dass du das selbst machen kannst. Und wenn es dann soweit ist, dass da dann Probleme auftreten, dann wende dich wieder ans Forum. 

Versteh mich bitte nicht falsch, ich will nicht deine Euphorie dämpfen, aber so, wie der Code logisch aufgebaut ist, sollte auch deine Vorgehensweise logisch aufgebaut sein.

Zusammenfassung:
Erstelle dein Layout fertig.
Arbeite damit, dass du dir deine Vorlagen aus den templates selbst in die Tabelle kopierst, händisch, einige Wochen.
Wenn du das dann 500x getan hast, und sich dabei eine Regel abzeichnet, dann schreib dazu den Code, der dir die Arbeit erleichtert.

Ein Vergleich aus der Autoindustrie: Du willst das Blech lackieren, bevor die Stanze daraus einen Kotflügel geformt hat. Und vielleicht wird aus dem, dann lackierten Blech, auch ne Heckklappe…

Anmerkung zum Schluss:
Da hast da Formeln drin, finde ich zwar gut, aber der Bereich, den diese Formeln durchsuchen müssen, bis Spalte 16.384…
Gehts noch?
Ist das Teil dann irgendwann voll mit Daten wirste Beiträge schreiben über langsames Excel.
Überdenke doch bitte nochmal dein Layout. Formeln dieses Bereichs sind gut, wenn sich die Werte ständig ändern. Wenn sich aber nix ändert, entferne die Formeln und füge die Werte ein.
Formeln lassen sich auch per VBA einfügen, und wenn sie ihren Dienst getan haben, wieder durch Werte ersetzen. Überleg dir das nochmal.

[ExcelNeuling99]

Hallo Wastl,

ja, vielleicht ist die Umsetzung erstmal zu aufwändig. Ich werde erstmal ohne Makro arbeiten und dann abwägen, ob ich im Laufe der Zeit ein Makro benötige.

Trotzdem vielen Dank!