Excel 2011: Bei Klick auf Button Makro je nach Zellinhalt ausführen

[fiedlator]

Ihr lieben Visual Basicianer: ich hab da mal wieder ein kleines Problemchen, was für geübte sicherlich ein Klacks ist. Folgender Sachverhalt: Ich habe ein Dropdown-Menü. Dieses kann verschiedene Werte annehmen. Bei Klick auf einen Button soll das gewünschte Makro je nach im Dropdown gewähltem Zellinhalt gewählt werden. Das wars eigentlich auch schon. Ich hab keine Ahnung, wie ich das am besten bewerkstellige. Ich denke, es müsste eine Reihe von if-Funktionen bedeuten. Das Dropdown-Menü kann insgesamt acht Werte annehmen, also sollen acht Makros hinterlegt werden.

Ach ja: Toll wäre es, wenn ich bei Klick auf den Button eine Meldung erhalten würde, falls die Zellen C3:C8 unvollständig sind. Also wenn nicht jede Zelle ausgefüllt ist.

Ich weiss, dass das alles nicht wirklich schwer ist. Doch leider bin ich nur Laie und komme maximal einmal im Quartal mit so was in Berührung .

Merci

 

[Mr. Snoot]

So?

 

[fiedlator]

Super ! Vielen Dank.
Eine Frage habe ich noch. Bei meinen Tests mit Makros konnte das Makro nicht per Crtl Z rückgängig gemacht werden. Für evtl. Fehler hätte ich jedoch gern die Möglichkeit, das letzte ausgeführte Makro rückgängig zu machen. Hierfür gibt es doch sicher auch einen recht einfachen Befehl, oder?

Merci

Edit: Die Msgbox wird angezeigt, dass aufgenommene Makro jedoch nicht gestartet. HAst du eine Idee, woran das liegen könnte?

Edit 2: Das Makro ansich ist funktionsfähig. Wenn ich es durch einen extra Button aufrufe, funktioniert es.

 

[Mr. Snoot]

Du musst im VBA-Editor bei den Zeilen 'Call makroX das Hochkomma entfernen und hinter Call den Namen deines jew. Makros schreiben.

​

Des Weiteren lassen sich durch Makros ausgeführte Aktionen nicht rückgängig machen! Dazu müsstest du im Grunde deine Ausgangstabelle kopieren und bei Bedarf wiederherstellen (per Makro); wenn man nur einzelne Aktionen rückgängig machen wollte, müsste man das alles jeweils so programmieren.

 

[fiedlator]

Ah..jetzt funktionierts...super, danke .
Allerletzte Frage....es wird noch mal knifflig.
Sinn und Zweck des makros ist es, dass die Rechnungen, die jeweilis einer von acht Kategorien entsprechen, in einer Tabelle unter der richtigen Überschrift stehen. Mein Makro sieht also recht simpel aus: "Schneide Zellen aus A3:A8 aus - füge eine neue Zeile unter (z.B.)" immaterielle Wirtschaftsgüter" ein und kopiere den Zelleninhalt hier rein. Das Problem ist natürlich, dass ich mit meinem simplen Verständnis die Zeile nur absolut adressiere. Das heisst, in der leeren Tabelle ist die Überschrift immaterielle Wirtschaftsgüter als zweite Überschrift in Zeile 10. Sind in der ersten Kategorie einige Rechnungen eingetragen, rutscht die zweite Überschrift z.B. auf Zeile 30. Er fügt aber bei Makro2 logischer Weise immer noch eine neue Zeile in Zeile 10 ein. Kurzum: Kann ich dem Makro sagen, dass es die neue Zeile immer unter der für das Makro jeweils festgelegten Überschriftenzeile einfügt und so also mit der Tabelle mitwächst!?

Ich hoffe, ich hab das halbwegs klar ausgedrückt

Besten Dank!

 

[Mr. Snoot]

Machen kann man so gut wie alles. Wie das letztendlich aussieht hängt davon ab, wie deine Tabelle genau aufgebaut ist.

Bsp: Anhang anzeigen Auswahl.rar; egal wo Makro 1 / Makro 2 / Makro 3 ... steht, die Zellen A5:A7 werden immer darunter kopiert.

 

[fiedlator]

Hey, vielen Dank für die große Mühe!
Es besteht aber noch folgendes Grundproblem: Wenn ich die Zellen unter Makro1 mit Zahlen fülle, und das Makro aktiviere, werden die bestehenden Zeichen überschrieben. Letztendlich soll ja aber eine neue Zeile eingefügt werden, so dass die Zeichen unter Makro1 nicht überschrieben, sondern nach unten geschoben werden.

Vielen vielen Dank noch mal im vorraus

 

[Mr. Snoot]

  For Each rZelle In rBereich.Cells
    If rZelle.Value = Range("A1").Value Then
      rKopie.Copy Destination:=rZelle.Offset(1, 0)
    End If
  Next rZelle

wird zu

  For Each rZelle In rBereich.Cells
    If rZelle.Value = Range("A1").Value Then
    For i = 1 To rZelle.CurrentRegion.Rows.Count [COLOR="Green"]'die aktuelle Zellenumgebung zeilenweise durchlaufen[/COLOR]
      If rZelle.Offset(i, 0) = "" Then [COLOR="green"]'wenn die Zelle bei Offset(i, 0) leer ist, ...[/COLOR]
        rKopie.Copy Destination:=rZelle.Offset(i, 0) [COLOR="green"]'dann kopiere die Werte nach Offset(i, 0) ...[/COLOR]
        Exit For [COLOR="green"]'und beende die Schleife[/COLOR]
      End If
    Next i
    End If
  Next rZelle

 

[fiedlator]

Juhu...werds gleich probieren...vielen Dank noch mal.
Ein paar kleine VBA-Kenntnisse hab ich ja auch, aber kannst du ein Buch empfehlen, indem man solche Dinge gesammelt nachschlagen kann?

 

[Mr. Snoot]

Leider nicht.

Hab mir das alles durch learning by doing angeeignet, als ich in der Arbeit mal ein Makro ändern sollte.

Ich find aber die Videotrainings von video2brain immer sehr gut. Da kannst du dir mal ein paar Videos vorab anschauen:

• http://www.video2brain.com/de/products-190.htm
• http://www.video2brain.com/de/products-112.htm
• http://www.video2brain.com/de/products-687.htm

 

[fiedlator]

Ach herje...ich muss das noch mal hochholen. Ich versuche das gleiche gerade in einer anderen Arbeit anzuwenden. Diesmal sollte ein Spaltenbereich kopiert werden und wie gehabt überprüft werden, ob bereits Daten in der Zelle stehen. Diesmal allerdings ohne if-Funktion. Meine Code sieht also so aus:

Sub auswahl()
Dim rZelle As Range
Dim rBereich As Range
Dim rKopie As Range
Set rBereich = Range("C3:C14")
Set rKopie = Range("C100:C112")



  For Each rZelle In rBereich.Cells
    For i = 100 To rZelle.CurrentRegion.Columns.Count 'die aktuelle Zellenumgebung zeilenweise durchlaufen
      If rZelle.Offset(i, 0) = "" Then 'wenn die Zelle bei Offset(i, 0) leer ist, ...
        rKopie.Copy Destination:=rZelle.Offset(i, 0) 'dann kopiere die Werte nach Offset(i, 0) ...
        Exit For 'und beende die Schleife
      End If
    Next i
  Next rZelle

  
End Sub

Leider funktioniert es mal wieder nicht ^^

 

[Mr. Snoot]

Wie viele Spalten hat denn bei dir rZelle.CurrentRegion.Columns.Count? Wenn deine aktuelle Zelle = C1 ist, und es einen zusammenhängenden Bereich bis G10 gibt, dann wäre das Ergebnis 5 (C-D-E-F-G).

Deine Schleife läuft von i = 100 bis i = rZelle.CurrentRegion.Columns.Count. Sofern rZelle.CurrentRegion.Columns.Count nicht größer als 100 ist, wird die For-Schleife gar nicht durchlaufen.

Wenn rZelle.CurrentRegion.Columns.Count in deinem Fall gewollt kleiner als 100 ist und die For-Schleife rückwärts durchlaufen werden soll (was ich mal bezweifle ), muss hinten eine negative Schrittweite angegeben werden (Standard = +1):

For i = 100 To rZelle.CurrentRegion.Columns.Count Step -1​

 

[fiedlator]

Ok, dann werde ich das noch mal probieren....waren erwartungsgemäß mehr Fehler drin, als gedacht ^^.

Wir switchen noch mal zur obigen Problematik . Siehe Post nummer 8. Dass er die Zeilen durchläuft, funktioniert so weit. Das Problem: Die Kategorien der Zeilen verschieben sich ja zunehmend durch das hinzufügen von Zeilen. Einer Kategorie, die zunächst in Zeile 10 steht, steht später in Zeile 50, wenn in die kategorie über dieser 40 Rechnungen eingetragen wurden. Das muss das Programm natürlich bedenken. Ich müsste dem Makro also sagen können, dass es die Zeilenumgebung durchläuft, dabei in jedem Fall jedoch unter einem für jedes Makro festelegten Wert (z.B. geringwertige Wirtschaftsgüter) kommt.

Ideeen?

Danke wieder mal

 

[Mr. Snoot]

Das sollte mit obigem Code doch funktionieren!?

Aber in deinem letzten Beispiel hast du auch nicht mehr mit der Zeilenanzahl gearbeitet, sondern mit der Anzahl der Spalten (CurrentRegion.Columns.Count). Außerdem hast du wie gesagt bei 100 angefangen zu zählen, was vermutlich falsch ist. Ansonsten müsste natürlich auch dein Endwert wie gesagt größer als 100 sein (bei dir evtl. For i = 100 To 100 + CurrentRegion.Columns.Count; wenn du bspw. von 100 bis meinetwegen 112 laufen möchtest (wenn CurrentRegion.Columns.Count = 12 wäre)).

CurrentRegion.Rows/Columns.Count zählt, wie viele ununterbrochen Zeilen/Spalten es von der aktuellen Zelle aus gibt. Fügst du in diesem Bereich eine neue Zeile/Spalte ein, dann ist das Ergebnis automatisch eins mehr. Das wird also berücksichtigt.

CurrentRegion.Rows/Columns.Count ist aber immer relativ. Hast du eine einzige Zelle, dann spuckt dir CurrentRegion.Rows/Columns.Count immer 1 aus. Egal, ob das Zelle A1 oder Zelle X523 ist.

 

[fiedlator]

Sorry...das war eben das verwirrende. Diese Frage bezieht sich jetzt wieder auf obige Problematik...also die mit den Zeilen. Die Spaltenproblematik betrifft ein anderes Thema. Ich brauch diese Funktion also in zwei unterschiedlichen Arbeiten.

Noch einmal kurz zum Verständnis: Das obige Makro beherrscht diese Funktion bereits? Dann ist die Frage: wie? Also ich kann dem Makro ja sagen, dass es erst bei Zeile x anfangen soll, zu zählen. Aber wenn die Kategorien wachsen, verschiebt sich das doch? Das heisst, mit dem obigen Beispiel kann ich die Zielzelle doch nur absolut adressieren, und nicht relativ, so dass sie mitwächst. oder?

 

[Mr. Snoot]

Kannste bitte mal ne Beispieltabelle basteln, damit ich mal sehe, was sich wohin verschiebt bzw. von wo bis wo du zählen willst?

 

[fiedlator]

Hey, ich arbeite gerade an einer Excel, die beide Probleme noch einmal darstellt. Aber vorher hab ich noch ein paar Verständnisfragen...viell bekomm ich die Sache ja dann auch alleine behoben!? Danke schonmal...wie immer

For Each rZelle In rBereich.Cells

rbereich ist die Grenze, bis wohin kopiert werden soll? Das heisst ich lege fest, wie groß die Tabelle schlussendlich maximal sein kann?

For i = 1 To rZelle.CurrentRegion.Rows.Count 'die aktuelle Zellenumgebung zeilenweise durchlaufen

i ist der Beginn, wo das Programm anfängt zu zählen? Sprich i=10 heisst, dass das Programm erst bei der 10ten Zeile anfängt zu zählen?

If rZelle.Offset(i, 0) = ""

Was heisst in diesem Kontext offset? Bzw. warum ist die steht hinter dem Komma eine 0?

Und eine vielleicht etwas verrückte Frage: DIeses Makro enthält ja bereits einen Kopierbefehl. Macht dies ein zusätzliches Makro zum Kopieren überflüssig? Das sollte so sein, oder?

 

[Mr. Snoot]

rBereich ist der Zellenbereich, in dem deine Zelle liegen kann.

For Each rZelle in in rBereich.Cells durchläuft jede Zelle im vorher definierten Zellbereich rBereich.

-----------------------------------------------

i ist erstmal einzig und allein der Zähler für die For-Schleife. Diese zählt von i bis zum angegebenen Endwert. Dementsprechend ist For i = 1 To 20 genau dasselbe wie For i = 101 To 120. Die Schleife wird in beiden Fällen 20-mal durchlaufen.

Was du dann innerhalb der Schleife machst, kann von i völlig unabhängig sein. Wenn du schreibst:

For i = 1 To 20
..MsgBox "Hallo"
Next i​

wird einfach 20-mal Hallo ausgegeben.

Du kannst i natürlich auch verwenden um einen Zellbereich zu durchlaufen:

For i = 1 To 20
..Range("A" & i) = "Hallo" 'in Spalte A, Zeile i wird Hallo ausgegeben
Next i​

Dann entspricht i der Zeile, und in A1 bis A20 wird Hallo geschrieben. Dasselbe macht aber auch:

For i = 101 To 120
..Range("A" & i - 100) = "Hallo" 'in Spalte A, Zeile (i minus 100) wird Hallo ausgegeben
Next i​

Genauso gut kannst du eine ganz andere Variable verwenden:

m = 1
For i = 1 To 20
..Range("A" & m) = "Hallo" 'in Spalte A, Zeile m wird Hallo ausgegeben
..m = m + 1 'm um +1 hochzählen
Next i​

-----------------------------------------------

Offset gibt ausgehend vom aktuellen Bereich an, wie weit der Bereich verschoben werden soll. Der erste Parameter gibt die Anzahl an Zeilen an, der zweite die Anzahl der Spalten.

Range("E10").Offset(1, -2) verschiebt die Zelle um 1 Zeile nach unten und um 2 Spalten nach links (positive Werte sind nach rechts bzw. unten; negative nach links bzw. oben). Das Ergebnis wäre also dasselbe wie Range("C11")

DIeses Makro enthält ja bereits einen Kopierbefehl. Macht dies ein zusätzliches Makro zum Kopieren überflüssig? Das sollte so sein, oder?

Kommt darauf an, was du wohin kopieren willst

 

[fiedlator]

Ah, jetzt ist mir wieder etwas klarer und das Spaltenproblem ist tatsächlich gelöst. Alles funktioniert . Jetzt besteht nur noch das Zeilenproblem (siehe Eingangspost), dass ich jetzt der EInfachheit halber noch mal erstellt habe.

Ich habe also für Kategorie A und B adressiert, wo die neue Zeile eingefügt werden soll. Führt man jedoch drei mal makro A aus, werden neue Spalten eingefügt, so dass Kategorie B nun unter der für B neu erstellten Spalte ist. Das ist das Problem

 

[Mr. Snoot]

Das kann man ganz anders lösen - und wenn man eine Beispieltabelle hat, sieht man das auch sofort