Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1768to1772
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1768to1772
1768to1772
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Makro Formeln automatisch runterkopieren

Makro Formeln automatisch runterkopieren
08.07.2020 15:08:15
Tar04

Hallo Zusammen,
kann mir jemand helfen ein Code zu erstellen die folgendes erreicht:
Sobald auf Tabellenblatt 3 (TB3) beginnend bei Zelle A3 Werte eingefügt werden (Beispiel Zelle A3 bis A7 sind befüllt, also 5 Zeilen ), sollen die Formeln bei Tabelleblatt 2 (TB2) runterkopiert werden um genau 5 Zeilen.
Die Formeln aus TB2 beziehen sich sowohl auf TB3 als auch auf andere Tabellenblätter.
Beispiel Excel: https://www.herber.de/bbs/user/138865.xlsx
Ich will mit den manuellen Aufwand sparen, die formeln aus TB2 immer runter zu kopieren, da die Originaldatei riesig ist und ich dann Teileweise suchen muss bis wohin es kopiert wurde und ggf. nochmal runter ziehen muss usw.
Vielen Dank vorab :)

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro Formeln automatisch runterkopieren
08.07.2020 21:22:25
Werner
Hallo,
vielleicht so? 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim loLetzte As Long
Application.ScreenUpdating = False
If Target.Column = 1 Then
If Target.Row > 2 Then
loLetzte = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("TB2")
If loLetzte > 2 Then
.Range("A7:A" & loLetzte + 4).FormulaLocal = "='TB3'!A3"
.Range("B7:B" & loLetzte + 4).FormulaLocal = "='TB3'!D3"
.Range("C7:C" & loLetzte + 4).FormulaLocal = "=SVERWEIS($B:$B;'TB1'!$A$2:$B$7;2; _
0)"
End If
End With
End If
End If
End Sub
Code gehört ins Codemodul vom Tabellenblatt "TB3"
Rechtsklick auf den Tabellenblattreiter - Code anzeigen - Code rechts ins Codefenster kopieren.
Gruß Werner
Anzeige
AW: Makro Formeln automatisch runterkopieren
09.07.2020 13:48:02
Tar04
Hallo Werner,
vielen Dank!
Der Code funktioniert, Vielen Dank!!!
Ich habe jetzt weitere Formeln rein getan aber leider kommt mir eine Fehlermeldung wenn ich "wenn"-Formeln benutze. Muss ich hierfür was beachten?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim loLetzte As Long
Application.ScreenUpdating = False
If Target.Column = 1 Then
If Target.Row > 2 Then
loLetzte = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Auswertung")
If loLetzte > 2 Then
.Range("A7:A" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!A3"
.Range("B7:B" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!C3"
.Range("C7:C" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!E3"
.Range("D7:D" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!D3"
 .Range("E7:E" & loLetzte + 4).FormulaLocal = "=WENNFEHLER(WENN(SUCHEN("G-*";' _
Hier Daten einfügen'!C3)>0;'Hier Daten einfügen'!E3;WECHSELN('Hier Daten einfügen'!C3&'Hier Daten einf?gen'!D3;" ";""));WECHSELN('Hier Daten einfügen'!C3&'Hier Daten einf?gen'!D3;" ";""))"
.Range("J7:J" & loLetzte + 4).FormulaLocal = "=If(SVERWEIS($F:$F;'Pr?fung 1'!$A$ _
2:$Z$1048576;5;0)="";"Nein";"Ja")"
End If
End With
End If
End If
End Sub

Anzeige
AW: Makro Formeln automatisch runterkopieren
09.07.2020 13:53:58
Werner
Hallo,
1. alle "Gänsefüßchen" innerhalb der Formel müssen gedoppelt werden
2. die Formel im Code ist ein String. Wenn du da innerhalb der Formel dann im Code einen Zeilenumbruch mit _ vornimmst, dann musst du am Ende den String mit einem Gänsefüßchen abschließen und in der neuen Zeile mit einem & und Gänsefüßchen wieder beginnen.
Also so: 



.FormulaLocal = "=Formelbeginn" _
&"Fortsetzung der Formel"
Gruß Werner
AW: Makro Formeln automatisch runterkopieren
09.07.2020 15:36:25
Tar04
Muss ich ein Befehl geben, damit er ausführt?
Habe jetzt folgendes drin, aber es macht nichts:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim loLetzte As Long
Application.ScreenUpdating = False
If Target.Column = 1 Then
If Target.Row > 2 Then
loLetzte = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Auswertung")
If loLetzte > 2 Then
.Range("A7:A" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!A3"
.Range("B7:B" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!C3"
.Range("C7:C" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!E3"
.Range("D7:D" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!D3"
.Range("E7:E" & loLetzte + 4).FormulaLocal = "=IFERROR(IF(SEARCH(""G-*"",'Hier  _
Daten einf?gen'!C3)>0,'Hier Daten einf?gen'!E3,SUBSTITUTE('Hier Daten einf?gen'!C3&'Hier Daten einf?gen'!D3,"" "","""")),SUBSTITUTE('Hier Daten einf?gen'!C3&'Hier Daten einf?gen'!D3,"" "",""""))"
.Range("F7:F" & loLetzte + 4).FormulaLocal = "=SVERWEIS($E:$E;'Alle'!$F$3:$AP$ _
123319;37;0)"
.Range("G7:G" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
G$1048576;7;0)"
.Range("H7:H" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
G$1048576;4;0)"
.Range("I7:I" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
Z$1048576;26;0)"
.Range("J7:J" & loLetzte + 4).FormulaLocal = "=IF(SVERWEIS($F:$F;'Pr?fung 1'!$A$ _
2:$Z$1048576;5;0)="""";""Nein"";""Ja"")"
.Range("K7:K" & loLetzte + 4).FormulaLocal = "=SVERWEIS(SVERWEIS($E:$E;Alle!$F$ _
3:$AP$1048576;37;0);'Pr?fung 1'!$A$2:$F$1048576;6;0)"
.Range("L7:L" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
V$1048576;22;0)"
.Range("M7:M" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
W$1048576;23;0)"
.Range("N7:N" & loLetzte + 4).FormulaLocal = "=IF(UND(F7=""G-Satz*"";SVERWEIS($ _
F:$F;'Pr?fung 1'!$A$2:$H$1048576;8;0)=""C"");""R?derabverkauf!!!"";IF(ODER(M7=""x"";K7=""Ja"");IF(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$H$1048576;8;0)=""C"";""R?derabverkauf!!!"";"""");""""))"
.Range("O7:O" & loLetzte + 4).FormulaLocal = "=IF(N7=""R?derabverkauf!!!"";"" _
KEIN VERKAUF"";IF(UND(K7=""Ja"";L7=""Neu"";M7=""x"");""ACHTUNG!!"";IF(ODER(K7=""Ja"";L7=""Neu"";M7=""x"");""Vorsicht"";""ok"")))"
.Range("P7:P" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!G3"
.Range("Q7:Q" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
Y$1048576;24;0)"
.Range("R7:R" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
Y$1048576;25;0)"
.Range("S7:S" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
J$1048576;10;0)"
.Range("T7:T" & loLetzte + 4).FormulaLocal = "=IF(UND(SVERWEIS($F:$F;'Pr?fung 1' _
!$A$2:$S$1048576;13;0)>0;SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;14;0)>0);MITTELWERT(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;13;0);SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;14;0));IF(UND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;13;0)=0;SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;14;0)>0);SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;14;0);IF(UND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;13;0)>0;SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;14;0)=0);SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$S$1048576;13;0);""-"")))"
.Range("U7:U" & loLetzte + 4).FormulaLocal = "=IF(AND(SVERWEIS($F:$F;'Pr?fung 1' _
!$A$2:$J$1048576,15,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)>0),AVERAGE(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)),IF(AND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0)>0,SVERWEIS($F:$F;" & _
"'Pr?fung 1'!$A$2:$J$1048576,16,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)=0) _
,AVERAGE(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0),VSVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)),IF(AND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)=0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)>0),AVERAGE(" & _
"SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$ _
1048576,17,0)),IF(AND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0)=0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)>0),AVERAGE(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)),IF(AND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576" & _
",15,0)=0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)=0,SVERWEIS($F:$F;'Pr?fung 1'! _
$A$2:$J$1048576,17,0)>0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0),IF(AND(SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0)=0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)=0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0),IF(AND(" & _
"SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,15,0)=0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$ _
1048576,16,0)>0,SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,17,0)=0),SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$J$1048576,16,0),""-"")))))))"
.Range("V7:V" & loLetzte + 4).FormulaLocal = "=SVERWEIS(Auswertung!$F:$F;'Pr? _
fung 1'!$A$2:$S$1048576;19;0)"
.Range("X7:X" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
AC$1048576;27;0)"
.Range("Y7:Y" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
AC$1048576;28;0)"
.Range("Z7:Z" & loLetzte + 4).FormulaLocal = "=SVERWEIS($F:$F;'Pr?fung 1'!$A$2:$ _
AC$1048576;29;0)"
.Range("AB7:AB" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!N3"
.Range("AC7:AC" & loLetzte + 4).FormulaLocal = "='Hier Daten einf?gen'!O3/P7"
.Range("AD7:AD" & loLetzte + 4).FormulaLocal = "=AB7*(1-AE7)"
.Range("AE7:AE" & loLetzte + 4).FormulaLocal = "=IF(AI7=""Max. 35%"";0,35;IF( _
AI7=""Max. 50%"";0,5;IF(AI7=""ok"";AG7;IF(UND(AI7=""Anpassen!!"";AB70,35);""Max. 35%"";IF(SUMME(X7:Y7)>0;IF((AD7/IF(UND(T7>0;U7>0);MITTELWERT(T7;U7);IF(UND(T7=0;U7>0);U7;IF(UND(T7>0;U7=0);T7;""""))))-1>-0,5;""ok"";""Anpassen!!"");IF(UND(X7=0;Y7=0;Z7>0;K7=""Ja"";M7=""x"";AE7>0,5);""Max. 50%"";IF(UND(SUMME(X7:Z7)=0;O7=""Vorsicht"";S70,5);""Anpassen!!"";""ok"")))));IF(UND(AI7=""Anpassen!!"";AB70;IF((AD7/IF(UND(T7>0;U7>0);MITTELWERT(T7;U7);IF(UND(T7=0;U7>0);U7;IF(UND(T7>0;U7=0);T7;""""))))-1>-0,5;""ok"";""Anpassen!!"");IF(UND(X7=0;Y7=0;Z7>0;K7=""Ja"";M7=""x"";AE7>0,5);""Max. 50%"";IF(UND(SUMME(X7:Z7)=0;O7=""Vorsicht"";S70,5);""Anpassen!!"";""ok"")))))))"
.Range("AG7:AG" & loLetzte + 4).FormulaLocal = "=$AH$4"
.Range("AH7:AH" & loLetzte + 4).FormulaLocal = "=IF(AF7=""Anpassen!!"";(-0,5*( _
IF(UND(T7>0;U7>0);MITTELWERT(T7;U7);IF(UND(T7=0;U7>0);U7;IF(UND(T7>0;U7=0);T7;"""")))))/AB7+1;""-"")"
.Range("AI7:AI" & loLetzte + 4).FormulaLocal = "=IF((AB7*(1-AG7))=0;""Anpassen!! _
"";IF(O7=""KEIN VERKAUF"";""KEIN VERKAUF"";IF(O7=""Achtung!!"";(IF(UND(ODER(UND(X7=0;Y7=0);S70,35);""Max. 35%"";IF(SUMME(X7:Y7)>0;IF(((AB7*(1-AG7))/IF(UND(T7>0;U7>0);MITTELWERT(T7;U7);IF(UND(T7=0;U7>0);U7;IF(UND(T7>0;U7=0);T7;""""))))-1>-0,5;""ok"";""Anpassen!!"");IF(UND(X7=0;Y7=0;Z7>0;K7=""Ja"";M7=""x"";AG7>0,5);""Max. 50%"";IF(UND(SUMME(X7:Z7)=0;O7=""Vorsicht"";S70,5);""Anpassen!!"";""ok"")))));IF(SUMME(X7:Y7)>0;IF(((AB7*(1-AG7))/IF(UND(T7>0;U7>0);MITTELWERT(T7;U7);IF(UND(T7=0;U7>0);U7;IF(UND(T7>0;U7=0);T7;""""))))-1>-0,5;""ok"";""Anpassen!!"");IF(UND(X7=0;Y7=0;Z7>0;K7=""Ja"";M7=""x"";AG7>0,5);""Max. 50%"";IF(UND(SUMME(X7:Z7)=0;O7=""Vorsicht"";S70,5);""Anpassen!!"";""ok""))))))"
.Range("AJ7:AJ" & loLetzte + 4).FormulaLocal = "=AD7*P7"
End If
End With
End If
End If
End Sub

Anzeige
AW: Makro Formeln automatisch runterkopieren
09.07.2020 16:22:52
Werner
Hallo,
das ist vollkommener Schrott (sorry).
Das FormulaLocal 
.Range("P7:P" & loLetzte + 4).FormulaLocal =

hier erfordert, dass die Formeln in Deutscher schreibweise vorliegen.
Das bedeutet Deutsche Formel und Semikolon (;) als Parametertrenner.
Du mischst aber mal munter innerhalb der Formeln die Deutsche und die Englische Formelschreibweise.
Also entweder .FormulaLocal und dann durchgängig die Deutsche Formelschreibweise oder aber .Formula und dann durchgängig die Englische Formelschreibweise mit Komma (,) als Parametertrenner.
Zudem ist das absoluter Mist die Formeln spaltenweise in die jeweiligen Spalten zu schreiben.
Vorschlag:
Oberhalb deiner eigentlichen Tabelle eine Zeile einfügen. Die ganzen Formeln in diese eingefügte Zeile schreiben, anschließend diese Zeile ausbelnden.
Im Code dann einfach die komplette ausgeblendete Zeile kopieren und im entsprechenden Bereich dann die Formeln in einem Rutsch mit einem einzigen Kopiervorgang einfügen.
Bemerkung am Rande:
Deine Originaldatei hat dann ja aber nicht wirklich viel mit der hier hochgeladenen Beispielmappe zu tun.
Wenn du deine Mappe entsprechend mit den Formeln vorbereitest und hier hochlädst, dann kann ich dir das Makro entsprechend anpassen.
Aber bitte nicht wieder eine derart beschnittene Beispielmappe. Der Aufbau sollte dann schon dem Original entsprechen.
Gruß Werner
Anzeige
AW: Makro Formeln automatisch runterkopieren
09.07.2020 17:13:56
Tar04
Hallo,
sorry, dachte wenn ich ein Beispiel hätte würde ich schon weiter kommen, aber war nicht der Fall.
Sobald ich eine Wenn-formel auf deutsch eingefügt habe, hat er mir eine Fehlermeldung angezeigt. Als ich alle "Wenn"s mit "if"s ersetzt habe ging es dann plötzlich.
Ich schicke dir mal den Link zu der Datei:
http://www.mediafire.com/file/dwsktge7gtstmpi/Probe_V2.xlsm/file
Habe die Daten jetzt frei erfunden und die Namen der Spalten geändert, aber so sieht die Datei aus.
Also sobald man auf dem Tabellenblatt "Hier Daten Einfügen" Daten einfügt, soll auf dem Tabellenblatt "Auswertung" die Formeln eingefügt werden.
Danke Dir vielmals
Anzeige
AW: Makro Formeln automatisch runterkopieren
09.07.2020 18:39:33
Werner
Hallo,
was soll ich mit einer Datei mit 24 MB. Zudem lässt sich die nicht öffnen, Fehlermeldung beschädigt.
Reduzier doch die Datei. Ich brauche nur die zwei betreffenden Tabellenblätter mit ein paar Beispieldaten und allen Formeln die kopiert werden sollen.
Gruß Werner

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige