Mit CommandButton Zellinhalt ändern und anschließend in der selben Spalte nach unten kopieren

Question

Hallo

meine Frage klingt vlt. einfach, aber bei der Lösungsfindung beiße ich mir immo die Zähne aus und hoffe auf Hilfe in diesem Forum.

Ich mache meine Frage an einem Beispiel fest:
in Zelle V51 (resp. 51, 22) steht die Formel =WENN(B51="";"";MIN($U$13:U51)). Das Ergebnis ist in meinem Beispiel -3,68.

Ich möchte jetzt diese Zelle V51 (resp. 51, 22) mit der Maus auswählen (oder mit dem Cursor dort hin navigieren) und dann mittels Klick auf einen CommandButton erreichen, dass ein Befehl ausgeführt wird, welcher mir den Zellinhalt wie folgt ändert:
=WENN(B51="";"";MIN($U$51:U51)) UND anschließend diese angepasste neue Formel z.B. 250 mal nach unten kopiert, hier bei meinem Beispiel bis Zelle V300 (oder noch besser -  wenn technisch machbar - bis zur letzten befüllten Zelle in dieser Spalte).
Mit ActiveCell müsste ja Excel wissen, in welcher Zelle ich mich aktuell genau befinde.

Ich hoffe es hat jemand die zündende Idee
Ciao der Revanchist

Answer 1

Hallo,

versuche es mal so:

Sub formel()
Dim lngLetzte As Long
Dim lngZeile As Long
Dim lngSpalte As Long
Dim i As Long

'Variablen zuweisen
lngZeile = ActiveCell.Row
lngSpalte = ActiveCell.Column

'letzte beschrieben Zeile in aktueller Spalte ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, lngSpalte).End(xlUp).Row

'Schleife zum Einfügen der Formeln
For i = lngZeile To lngLetzte
   Cells(i, lngSpalte).FormulaLocal = "=WENN(B" & i & "="""";"""";MIN($U$51:U" & i & "))"
Next i

End Sub

Gruß

M.O.

Answer 2

Danke M.O.

der erste Schritt ist gemacht. Aber die Routine macht noch nicht genau das was diese soll.

Vlt. hast du übersehen, dass die Formel welche angepasst und anschl. kopiert werden soll nicht =WENN(B51="";"";MIN($U13:U51)) sondern =WENN(B51="";"";MIN($U$13:U51)) lautet.
Das $-Zeichen nach dem U und vor der 13 ist wichtig. Das habe ich mal in deine Routine eingefügt.

Was aber entscheidend sein dürfte ist, dass nach Klick auf den entspr. CB die Formel eben keinen Bezug nimmt, auf die ActiveCell. Wenn das Prozedere beispielsweise in Spalte V ablaufen soll, dann ist die starre Beibehaltung der Spalte U als Refernez korrekt. Wenn das Ganze aber in Spalte AD (mit Bezug zur Spalte $AC) und dort vlt. erst ab Zeile 122 ablaufen soll, wäre es toll, wenn die Formel das auch noch könnte.
Ich dachte mit ActiveCell findet VBA immer den Ausgangspunkt resp. ordnet diesen der Formel zu.

Und noch ein kleines Problem ist beim Ausprobieren aufgetaucht. Mit meinem Wunsch bis zur letzten befüllten Zelle in Spalte „x„ habe ich mir ein Eigentor geschossen. Wenn die Tabelle resp. die Spalte schon z.B. 6000 Einträge hat und ich will bei Zelle V51 starten, dann dauert es bei meinem Rechner eine gefühlte Ewigkeit, bis alle Formeln bis nach ganz unten geändert wurden. Kann man die Routine auf einen Wert festlegen, wie oft der Einfügevorgang auszuführen ist - z.B. wenn in Zelle C10 (10, 3) der Wert 100 steht, dann eben 100 x?

Ich hoffe dass das jetzt nicht zu kompliziert ist.

Ciao der Revanchist  

Answer 3

Vlt. habe ich mich auch nicht präzise genug ausgedrückt.

In Zelle V2009 steht aktuell =WENN(B2009="";"";MIN($U$51:U2009)). Wenn ich mich in Zelle V2009 (2009, 22) positioniere und den neuen CommandButton mit der Routine anklicke, sollte anschließend in Zelle V2009 stehen: =WENN(B2009="";"";MIN($U$2009:U2009)).  
In Zelle V2010 dann =WENN(B2010="";"";MIN($U$2009:U2010)). In V2011 dann  WENN(B2011="";"";MIN($U$2009:U2011)) usw..
 

WENN die Zelle AD122 (mit der Formel aktuell: =WENN($B122="";"";MIN($AC$50:AC122)) ausgewählt wurde, sollte nach Klick auf den CB, dann =WENN($B122="";"";MIN($AC$122:AC122))  erscheinen. In AD123 dann =WENN($B123="";"";MIN($AC$122:AC123)) usw.

Sorry wenn es vorher etwas missverständlich ausgedrückt war.

Ciao der Revanchist

Answer 4

ich versuche mal einen weiteren Anlauf.

wenn es möglich wäre immer die aktuelle Position der gerade aktiven Zelle in einer vorher definierten Zelle des Exceltabellenblattes z.b. A7 abzubilden, dann könnte die von M.O. geschriebene Routine diese Zelle als Referenz für den Kopiervorgang und das anschließende Einfügeprozedere verwenden.

Nur wie schafft man es, diese Anzeige vlt. sogar gleich mit $ $ zu generieren (z.B $AC$122).
Wenn  die Aktivierung des Auslesens der aktuellen Position einen Auslöseimpuls bräuchte, dann könnte diese ja am Anfang der von M.O. vorgestellten Routine miterledigt werden.

Ciao der Revanchist

Answer 5

ich komme einfach nicht weiter und würde das Thema gern an dieser Stelle schließen und die letzte Frage, welche ja von der Ursprungsfrage deutlich abweicht, in einer neuen Anfrage starten
Danke an alle
Ciao der Revanchist

Answer 6

Hallo,

ich bin leider erst jetzt dazu gekommen, den Code zu ändern. Trotz deiner letzten Antwort, versuche es mal so:

Sub formel_neu()
Dim lngLetzte As Long
Dim lngZeile As Long
Dim lngSpalte As Long
Dim lngAnzahl As Long
Dim i As Long

'Variablen zuweisen
lngZeile = ActiveCell.Row
lngSpalte = ActiveCell.Column
lngAnzahl = Range("C10").Value

'letzte beschrieben Zeile in aktueller Spalte ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, lngSpalte).End(xlUp).Row

'Schleife zum Einfügen der Formeln
For i = lngZeile To lngZeile + lngAnzahl
   Cells(i, lngSpalte).FormulaLocal = "=WENN(B" & i & "="""";"""";MIN(" & Cells(51, lngSpalte - 1).Address & ":" & Cells(i, lngSpalte - 1).Address & "))"
Next i

End Sub

Dabei wird die Anzahl der zu ersetzenden Zeilen aus Zelle C10 gelesen und die Spalten in der MIN-Formel werden entsprechend der aktiven Zelle angepasst.

Gruß

M.O.

Answer 7

Hallo M.O

Danke für deine weitere Unterstützung. Das mit der Anzahl der zu ersetzenden Zellen(Zeilen) aus der C10-Vorgabe funktioniert einwandfrei.

Aber bei der Routine

Cells(i, lngSpalte).FormulaLocal = "=WENN(B" & i & "="""";"""";MIN(" & Cells(51, lngSpalte - 1).Address & ":" & Cells(i, lngSpalte - 1).Address & "))"
Next i

sperrt sich das Ganze an der 51. 

Die gerade aktive Zelle muss den Bezug für die Korrektur und das Kopieren und anschließend Einfügen sein. Diese habe ich mir eingerichtet und wird in Zelle A10 auf Klick eines weiteren Commandbutton händisch aktualisiert (wenn man möchte), A10 aktualisiert sich aber auch immer wieder einmal sebstständig wenn man irgendeine Zelle irgendwo im Tabellen blatt anwählt. Aber wie gesagt vor der von mir gewünschten Aktion habe ich auch kein Problem den CB 04 noch einmal separat anzuklicken.
So um die ganze Sache etwas zu veranschalichen habe ich mal eine kleine Screenshotreihe generiert, so dass mein Wunsch vlt. etwas deutlicher wird.

Die Ausgangssituation:

so sehen die Formeln in der Ausgangslage aus:

so jetzt wurde die Zelle V2006 (also 1 rechts von U2006) angeklickt:

und so soll die Tabelle aussehen, wenn die Routine über den CB 03 ausgelöst und nach Plan die Arbeit abgewickelt wurde

 

und diese Formeln stehen dann (hoffentlich) in den 3 gewünschten Zellen

Dieses Prozedere muss egal wo die Aktive Zelle angeklickt wird, funktionieren. Das kann z.B. auch Zelle AB144 oder CL 1461 sein

Die schwarze Schriftfarbe dient nur der Verdeutlichung wo die Einfügeaktion passiert sein sollte Hoffe jetzt ist

alles klar Ciao der Revanchist

Answer 8

Hallo,

dann wohl so:

Sub formel_neu()
Dim lngLetzte As Long
Dim lngZeile As Long
Dim lngSpalte As Long
Dim lngAnzahl As Long
Dim i As Long

'Variablen zuweisen
lngZeile = ActiveCell.Row
lngSpalte = ActiveCell.Column
lngAnzahl = Range("C10").Value

'letzte beschrieben Zeile in aktueller Spalte ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, lngSpalte).End(xlUp).Row

'Schleife zum Einfügen der Formeln
For i = lngZeile To lngZeile + lngAnzahl
   Cells(i, lngSpalte).FormulaLocal = "=WENN(B" & i & "="""";"""";MIN(" & Cells(lngZeile, lngSpalte - 1).Address & ":" & Cells(i, lngSpalte - 1).Address & "))"
Next i

End Sub

Gruß

M.O.

Answer 9

Prima M.O. !
aber einen kleinen Fehler meinerseits habe ich noch gefunden => im letzten Screenshot (mittlerweile habe ich das korrigiert, dabei aber übersehen, dass Du schon geantwortet hast)

Da schrieb ich  =WENN(B2006="";"";MIN($U$2006:$U$2006))
die Formel muss natürlich =WENN(B2006="";"";MIN($U$2006:$U2006)) in Zelle V2006 heißen, das letzte $ muss weg. Damit wird V2007 neu =WENN(B2007="";"";MIN($U$2006:$U2007));  V2008 neu =WENN(B2008="";"";MIN($U$2006:$U2008));

Leider finde ich in deinem Code dieses $ nicht, welches zu eliminieren wäre.(vermutlich schon recht peinlich für mich, aber auch daraus lerne ich vlt. wieder etwas für die Zukunft)

was muss ich korrigieren ?

Ciao der Revanchist

aktuelle INFO

ich habe ein wenig herumexperimentiert - durch Zufall oder  vielleicht vorher irgendetwas aus Versehen nicht richtig einkopiert - es funktioniert immo soweit wie ich das benötige.
Das letzte $ steht zwar immer noch in der Formel, aber die aktuelle Zeile wird mitgezogen, so dass es eigentlich egal ist, ob da ein $ steht oder nicht.
Falls irgendetwas doch noch Probleme macht, würde ich mich gerne noch einmal an Euich wenden.

Cias der Revanchist

Answer 10

Hallo,

die $-Zeichen werden automatisch generiert, wenn du die Zelladresse mit .Address einfügst. Probiere es einfach mal aus:

Range("A1") = Range("A1").Address

Da bei meinem geposteten Makro die letzte Zeilenadresse immer entsprechend eingefügt wird, ist es unschädlich, dass die Zelladressen absolut dargestellt werden.

Willst du das oben genannte Beispiel als $A1 darstellen, musst den Code wie folgt ergänzen:

Range("A1") = Range("A1").Address(rowabsolute = True, columnabsolute = False)

oder im Fall meines geposteten Makros:

Cells(i, lngSpalte).FormulaLocal = "=WENN(B" & i & "="""";"""";MIN(" & Cells(lngZeile, lngSpalte - 1).Address & ":" & Cells(i, lngSpalte - 1).Address(columnabsolute = False) & "))"

Das Rowabsolut kann man sich sparen, da True immer der Standardwert ist und diese nicht dargestellt werden müssen.

Gruß
M.O.