Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1544to1548
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
Inhaltsverzeichnis

Makro ändert Formel zum Jahreswechsel

Makro ändert Formel zum Jahreswechsel
25.02.2017 21:10:24
Florian
Hallöchen
Zum festigen meiner VBA Kentnisse habe ich vor einiger Zeit begonnen mir ein Programm zur Verwaltung meiner Kundenbestellungen zu schreiben.
Hieraus wurde schnell ein "Programm" mit dem ich alles Handle was das Thema Bestellungen betrifft.
Die Originaldatei hat ca. 60 Seiten, hiervon sind 50, im Aufbau identische, Firmenseiten.
Ein Beispiel einer dieser Seiten habe ich hochgeladen:
https://www.herber.de/bbs/user/111763.xlsx
Hier nun ein Problem, bei dem ich alleine nicht weiter komme.
Zum Jahreswechsel müssen meine Formeln angepasst werden, dies würde ich gerne automatisieren.
Hierfür folgende Idee zur Auslösung des Ereignisses:
Ich speichere beim beenden der Datei das Datum auf einer Seite.
Beim Starten vergleiche ich dieses Datum mit der Systemzeit.
Wenn die Jahreszahl-Systemzeit 1 größer ist, als die gespeicherte Löse ich das Event aus.
Ich speichere nun (beim Jahreswechsel 2018) den Umsatz 2016 auf der Übersichtsseite, ändere jeweils Formeln und Jahreszahlen auf der Firmenseite.
Soweit kein Problem.
Jedoch tritt ein Problem auf, wenn ich dann im Jahr 2018 noch eine Rechnung aus 2017 bekomme, zu der ich keine offene Bestellung habe.
Der Umsatz dieser Rechnung würde ja nun zum Umsatz 2018 gezählt werden.
(Ich hoffe ich konnte das Problem verständlich darstellen :D )
Nun zwei Fragen.
1. macht mein Eventeregniss Sinn, oder gibt es das was eleganteres?
2. gibt es eventuell eine Summenformel, die Spalte H (Rechnungsdatum) berücksichtigt? - also z.B. wenn H6 > 2016 aber

29
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
25.02.2017 21:48:06
onur
Hi,
Ich sehe in der Beispieldatei keine Formeln,die sich auf das Jahr beziehen.Also was willst du ändern?
Du kannst doch die Kundenseite einfach kopieren und leeren, dann hast du ein neues Blatt für 2018.
Aber warum musst du für jedes Jahr ein neues blatt haben? Kannst du nicht einfach das blatt im nächsten Jahr witerführen?
AW: Makro ändert Formel zum Jahreswechsel
25.02.2017 21:50:14
Florian
Hmm.
Dann war meine Beschreibung wohl nicht so gut :D
Das Blatt soll natürlich einfach weitergeführt werden.
Ich möchte nicht ein Blatt pro Jahr haben.
Auch beziehen sich die Formeln nicht auf das Jahr.
Aber wie gesagt, ich bekomme ein Problem, wenn ich im Jahr 2018 eine Rechnung aus 2017 bekomme, dann stimmt meine Formel nicht mehr.
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
25.02.2017 21:54:32
onur
Sag mir mal welche formel du meinst, dann schauen wir mal.
AW: Makro ändert Formel zum Jahreswechsel
25.02.2017 21:56:48
Florian
Ich meine J2 und J3.
Wenn nun Jahreswechsel ist, kopiere ich die Formel auf J2 in J3.
In der nun leeren J2 muss ich nun eine neue Formel für den Umsatz 2018 einfügen.
Wenn es aber noch keine Bestellung für 2018 gibt, weiß ich nicht wie die Formel lauten soll-.
AW: Makro ändert Formel zum Jahreswechsel
25.02.2017 23:55:49
onur
Teste mal Version 2.
https://www.herber.de/bbs/user/111768.xlsx
Hier kannst du einfach unten weiterschreiben.
Die Blattnamen müssen identisch mit den Firmennamen in Tabelle 2 sein, damit das richtige Blatt für die Übersicht gefunden wird.
Ich weiss nicht, was relevant ist, Lieferdatum, Rechnungsdatum oder "Datum", ich habe einfach Spalte "Datum" genommen.
Gruß
Onur
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 06:25:46
fcs
Hallo Onur, hallo Florian,
die SUMMEWENNS-Formeln kann mann noch etwas optimieren, wenn man das Jahr in der Zelle mit in die Formeln einbaut. So muss man bei Jahreswechsel "nur" noch die Jahreszahlen ändern, die Formeln bleiben unverändert.
Als Alternative könnte man auch mit der Funktion SUMMENPRODUKT arbeiten.
Tabellennamen und Spalten gearbeitet. SO ist sichergestellt, dass immer alle Datenzeilen in den Formeln summiert werden, wenn Daten hinzugefügt werden.
Firmaname

 IJK
1 SummenproduktSummewenns
22017 1.221,301221,30
32016 6.805,936805,93

Formeln der Tabelle
ZelleFormel
J2=SUMMENPRODUKT((JAHR(TabelleAquaGlobal[Datum])=I2)*(TabelleAquaGlobal[Net19]+TabelleAquaGlobal[Net7]))
K2=SUMMEWENNS(TabelleAquaGlobal[Net19];TabelleAquaGlobal[Datum];">=" & DATUM(I2;1;1); TabelleAquaGlobal[Datum];"<="&DATUM(I2;12;31))+SUMMEWENNS(TabelleAquaGlobal[Net7];TabelleAquaGlobal[Datum];">=" & DATUM(I2;1;1); TabelleAquaGlobal[Datum];"<="&DATUM(I2;12;31))
J3=SUMMENPRODUKT((JAHR(TabelleAquaGlobal[Datum])=I3)*(TabelleAquaGlobal[Net19]+TabelleAquaGlobal[Net7]))
K3=SUMMEWENNS(TabelleAquaGlobal[Net19];TabelleAquaGlobal[Datum];">=" & DATUM(I3;1;1); TabelleAquaGlobal[Datum];"<="&DATUM(I3;12;31))+SUMMEWENNS(TabelleAquaGlobal[Net7];TabelleAquaGlobal[Datum];">=" & DATUM(I3;1;1); TabelleAquaGlobal[Datum];"<="&DATUM(I3;12;31))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Da es etwas mühsam ist, die Formeln mit den Tabellen-Namen anzupassen nachfolgend noch ein Makro zum Einfügen der SUMMENPRODUKT-Formeln auf den Kundenblättern.
Und ein Makro zur änderung der Jahreszahl auf den Kundenblättern.
LG
Franz
Sub FormelnAnpassen() Dim wks As Worksheet Dim strFormel As String, strTab As String If MsgBox("Formeln auf den Kundenblättern anpassen?", vbOKCancel, _ "SUMMENPRODUKTFORMEL einfügen") = vbCancel Then Exit Sub For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Übersicht", "Tabelle3" 'in diesen Tabellenblättern nicht Case Else With wks If .Range("E2").Text = "K.Nr." And .Range("E3").Text = "Lieferzeit" Then 'Zusatzprü _ fung strTab = .ListObjects(1).Name strFormel = "=SUMPRODUCT((YEAR(" & strTab & "[Datum])=RC[-1])*(" _ & strTab & "[Net19]+" & strTab & "[Net7]))" .Range("J2:J3").FormulaR1C1 = strFormel End If End With End Select Next End Sub Sub NeuesJahr() Dim wks As Worksheet Dim Jahr As Integer If MsgBox("Jahr auf den Kundenblättern wechseln?", vbOKCancel, _ "Jahreswechsel") = vbCancel Then Exit Sub For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Übersicht", "Tabelle3" 'in diesen Tabellenblättern nicht Case Else With wks If .Range("E2").Text = "K.Nr." And .Range("E3").Text = "Lieferzeit" Then 'Zusatzprü _ fung Jahr = .Range("I2") .Range("I2").Value = Jahr + 1 .Range("I3").Value = Jahr End If End With End Select Next End Sub
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 17:39:44
Florian
Hallo ihr beiden.
Die Lösung von fcs geht in genau die richtige Richtung.
Wenn ich das richtig sehe steht in J2 und K2 letzlich das gleiche, mit zwei unterschiedlichen Formeln (nur halt kürzere und längere Version).
Ich nehme mir also die Formel aus J2 - zur Sicherheit:
=SUMMENPRODUKT((JAHR(TabelleAquaGlobal[Datum])=I2)*TabelleAquaGlobal[Net19]+TabelleAquaGlobal[Net7]))
Es wird in das Datum aus I2 in der Spalte Datum von TabelleAquaGlobal gesucht, wenns gefunden wird dann wird die gleiche Zeile Spalte Net19 und Net7 addiert.
Ändere ich also das Datum in I2 (bei jetziger Tabelle) in 2018, wäre mein Ergebnis 0,00.
Wenn nun Jahreswechsel ist, muss ich nur noch den Umsatz von 2016 auf mein Übersichtsblatt kopieren und die beiden Jahreszahlen in I2 und I3 um 1 erhöhen?
Habe ich die Formeln und das Makro so richtig verstanden?
Wenn ja, wäre die erste Frage geklärt :)
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 18:38:11
Florian
So, minimalste Änderung in deinem Code und die Formel steht überall - ich danke dir vielmals:)
Kommen wir zum Jahreswechsel
1. Das Auslösen des Ereignisses
Wie Eingangs erwähnt würde ich beim schließen der Datei das Datum speichern:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("Einstellungen")
.Range("A1") = Format(Date, "YYYY")
If .Range("E4") = True Then ActiveWorkbook.Save
If .Range("G7") = True Then Call DelDatensicherung
End With
End Sub
Beim starten prüfe ich nun, ob die Systemzeit ein Jahr weiter ist:
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Einstellungen")
If Year(Date) = .Range("A1") + 1 Then
MsgBox "Jahreswechsel"
Exit Sub
End If
If .Range("E5") = True Then Application.OnTime Now, "letzteDatensicherung"
End With
End Sub

Wenn dies so ist starte ich das Makro (anstelle der Msgbox) und beende das Makro, damit "letzteDatensicherung" nicht gestartet wird.
Dieses Makro prüft ob die letzte Datensicherung älter als x Tage ist (einstellbar in den Einstellungen) und startet nach Abfrage vbyesno eine Datensicherung (und beendet hierfür das Programm).
Dieses und das Jahreswechselmakro könnten ja kollidieren.
Haut dieser Ansatz hin, oder fällt euch etwas eleganteres ein?
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 18:41:36
onur
Hi Florian,
Wenn sich ein Anderer in den Thread einmischt, halte ich mich von da aus raus aus dem Ganzen.
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 18:44:02
Florian
Oo
Meine erste Frage ist geklärt, ob die Lösung nun von dir oder einem anderen kam spielt doch keine Rolle?
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 19:03:59
onur
Weisst du, wenn ich sehe, dass schon jemand in einem Thread drin ist und dabei ist, dem Eröffner des Thread´s zu helfen, halte ich mich aus diesem Thread heraus, damit der Andere in Ruhe eine Lösung finden kann, es keine "Parallelerarbeitungen" gibt und es "unhöflich" wäre, mich auch noch einzubringen.
Das Gleiche erwarte ich eigentlich auch von Anderen.
Da Du aber auch noch auf "fcs" eingegangen bist, musst du dich ab jetzt auch an ihn richten.
Gruß
Onur
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 19:19:19
Florian
Nun, das sehe ich anders.
Viele Leute helfen wohl schneller - und dann gibt es meistens nur eine Lösung, die dem Ersteller passt - aber ok.
Ich bedanke mich trotzdem für deine Hilfe!
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 19:37:53
onur
Klar, für dich ist wäre es ideal, wenn 10 Leute gleichzeitig an deinem problem arbeiten, und du suchst das aus, was dir am besten passt.
Und dass die anderen 9 sich umsonst bemüht haben, muss dich ja nicht tangieren.
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 19:46:10
onur
Missverstehe mich bitte nicht, ich würde es an deiner stelle wahrscheinlich ebenso sehen.
Aber dass die anderen 9 das nicht so sehen, ist doch wohl verständlich.
Gruß
Onur
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
26.02.2017 21:31:42
fcs
Hallo Florian,
dein Ansatz für die Ereignismakros beim Öffnen und Schließen der Datei sollte funktionieren.
Ich würde da geringfügige Anpassungen machen.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("Einstellungen")
.Range("A1") = Year(Date) ' statt Format(Date, "YYYY") da Format(...) eigentlich Text  _
erzeugt
If .Range("E4") = True Then ActiveWorkbook.Save
If .Range("G7") = True Then Call DelDatensicherung
End With
End Sub
'Beim starten prüfe ich nun, ob die Systemzeit ein Jahr weiter ist:
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Einstellungen")
If Year(Date) = .Range("A1") + 1 Then
Call NeuesJahr
ElseIf .Range("E5") = True Then
Call letzteDatensicherung 'statt Application.OnTime Now, "letzteDatensicherung"
End If
End With
End Sub
Anmerkung zu Datensicherung:
Wenn man zum Erstellen einer Sicherungskopie die Methode SaveCopyAs verwendet statt SaveAs dann muss man die Datei nach dem Erstellen der Sicherungskopie nicht schliessen, da die Original-Datei geöffnet bleibt.
LG
Franz
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
27.02.2017 08:51:27
Florian
Guten Morgen Franz
Danke für deine "Schönheitskorrekturen" :)
Diese habe ich eingepflegt (noch mit der Msgbox "Jahreswechsel").
Das ganze funktioniert nach schließen der Datei, ändern der Systemzeit und neustarten der Datei einwandfrei :)
Ich mache mich nun daran, meine Übersichtsseite zu formatieren, denn hier muss ja am 02.01.2018 pro Worksheet ein Umsatz rein (dafür muss ich ja vorher wissen, wo der hin soll).
Sobald das fertig ist, schreibe ich deinen Jahreswechsel Code auf meine Bedürfnisse um und berichte.
Noch ein Nachtrag zu onur:
Nein, auch wenn ich einer der 9 Leute bin (und das bin ich oft genug, weil die Hilfsansätze, die ich mit meinen VBA Kentnissen bieten kann nur selten zur Lösung führen (zumindest nicht in meiner ursprünglichen Form).
Jedoch rege ich mich nicht auf, wenn eine andere Lösung meiner bevorzugt wird (das würde ich auch nicht, wenn meine "besser" wäre) - denn ich schreibe ins Forum, um dem Ersteller des Threads zu helfen.
Wenn das ein anderer besser macht - auch ok - Ziel erreicht.
Anzeige
AW: Makro ändert Formel zum Jahreswechsel
27.02.2017 08:55:20
Florian
Edit: (weiß leider nicht wie man hier editiert)
zu folgender Zeile (welche Ursprünglich so in meinem Makro stand)
Call letzteDatensicherung 'statt Application.OnTime Now, "letzteDatensicherung"
habe ich die Anmerkung gelesen, dass dieses Makro mit OnTime erst nach dem vollständigen Starten der Datei läuft, mit Call im Open event jedoch sofort.
Ich habe beides längere Zeit getestet und keinen unterscheid festgestellt, da mein Datensicherungsmakro den gesamten Ordern (sind jede Menge Dateien) mittels 7zip Konsolenprogramm zipt und im Ordner zbackup ablegt.
Dafür muss jedoch die Datei geschlossen werden - was mein Makro tut - wenn also nicht alles geladen wird ist das ja irgendwie egal.
Jedoch erschien mir dennoch die OnTime Lösung als "sauberer"
AW: Makro ändert Formel zum Jahreswechsel
27.02.2017 18:10:02
Florian
So, es nimmt gestalt an.
Vorweg ein neuer Upload einer Beispielmappe mit Übersichtsseite und zwei Firmenseiten, sowie ein erstes jahreswechselmarko.
https://www.herber.de/bbs/user/111808.xlsm
Hier mein Makro außerhalb der Testdatei mit zwei Fragen (im Code gekennzeichnet)

Sub Makro1()
Dim rng As Range
Dim wks As Worksheet
Dim strFehler As String
Dim i As Long
Dim lngletzteZeile As Long
With ThisWorkbook.Worksheets("Firmenübersicht")
'letzte Zeile
lngletzteZeile = .Cells(Rows.Count, 6).End(xlUp).Row
'Einfügen drei neuer Spalten und setzen der Formate
.Columns("G:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("G:I").ColumnWidth = 10
.Columns("G:I").HorizontalAlignment = xlCenter
.Columns("G:G").NumberFormat = "0.00"
.Columns("H:H").NumberFormat = "#,##0"
.Columns("I:I").NumberFormat = "0%"
'Setzen der Überschriften
.Range("G1").Value = Year(Date)
.Range("G1").NumberFormat = "0"
.Range("H1").Value = "#"
.Range("I1").Value = "%"
'Färben der Spalte G
With .Columns("G:G").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
'Schleife durchläuft alle Arbeitsblätter und schreibt Umsatz in Firmenübersicht
For Each wks In ActiveWorkbook.Worksheets
'Prüfung ob es sich um Firmenseite handelt
If wks.Name = wks.Range("E1") Then
Set rng = .Columns(6).Find(wks.Name, lookat:=xlWhole)
'Wenn gefunden, dann schreiben der Werte
If Not rng Is Nothing Then
rng.Offset(0, 1) = wks.Range("J3").Value
Else
'Speichern der nicht gefunden Firmen in einer Variablen
strFehler = wks.Name & " / " & strFehler
End If
End If
Next
'Schleife zum Schreiben der Formeln in Spalte H (#) und I (%)
For i = 2 To lngletzteZeile - 2
'Spalte H (#)
.Range("H" & i).Formula = "=G" & i & "-J" & i
'Spalte I (%)
''''''' Diese Formel bekomme ich nicht zum laufen
'''''''Laufzeitfehler 1004
'''''''Originalformel in Zelle I2
'''''''- =WENNFEHLER((G2-J2)/ABS(J2);"")
'''''''mein VBA Versuch
'''''''.Range("I" & i).Formula = "=IFERROR((J" & i & "-M" & i & ")/ABS(M" & i & ");" &  _
Chr(34) & Chr(34) & ")"
Next i
'Schreiben der Werste in letzteZeile (Summe)
.Range("G" & lngletzteZeile).Formula = "=SUM(G2:G" & lngletzteZeile - 2 & ")"
.Range("H" & lngletzteZeile).Formula = "=G" & lngletzteZeile & "-J" & lngletzteZeile
''''Hier die Formel mit ABS . Range("I" & lngletztezeile).Formula = .....
End With
'erneute Schleife durch alle Arbeitsblätter - Anpassen der Umsatzspalten
For Each wks In ActiveWorkbook.Worksheets
'Prüfung ob es sich um Firmenseite handelt
If wks.Name = wks.Range("E1") Then
'Pfüfung ob es bei aktueller Firma Fehler gab
'''' Hier die Abfrage, ob wks.name NICHT in der Variable strFehler vorkommt
'''' oder dieses wks überspringen, wenn es vorkommt
''''Haut nicht hin - If InStr(wks.Name, strFehler) > 0 Then
wks.Range("I3").Value = Year(Date) - 1
wks.Range("I2").Value = Year(Date)
End If
Next
'Wenn Variable strFehler belegt, dann zeigen der Fehler
If (strFehler)  "" Then MsgBox ("Fehler bei folgenden Firmen. Bitte manuell prüfen." & _
Chr(13) & strFehler)
End Sub
Dann habe ich jedoch noch ein logisches Problem auf meiner Übersichtsseite.
Spalte J ist ein Link auf Firmaseite Zelle J3 (Umsatz 16), der durch mein Makro kommt an diese Stelle ja der Umsatz 17.
Gibt es eine Möglichkeit hier anstelle die Formeln durch die absoluten Werte zu ersetzen?
Also die Formel lautet =Firma1J3 / Zellinhalt = 1.450 / dann die Formel entfernen und den Zellinhalt als Wert?
AW: Makro ändert Formel zum Jahreswechsel
27.02.2017 18:12:50
Florian
So, es nimmt gestalt an.
Vorweg ein neuer Upload einer Beispielmappe mit Übersichtsseite und zwei Firmenseiten, sowie ein erstes jahreswechselmarko.
https://www.herber.de/bbs/user/111808.xlsm
Hier mein Makro außerhalb der Testdatei mit zwei Fragen (im Code gekennzeichnet)

Sub Makro1()
Dim rng As Range
Dim wks As Worksheet
Dim strFehler As String
Dim i As Long
Dim lngletzteZeile As Long
With ThisWorkbook.Worksheets("Firmenübersicht")
'letzte Zeile
lngletzteZeile = .Cells(Rows.Count, 6).End(xlUp).Row
'Einfügen drei neuer Spalten und setzen der Formate
.Columns("G:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("G:I").ColumnWidth = 10
.Columns("G:I").HorizontalAlignment = xlCenter
.Columns("G:G").NumberFormat = "0.00"
.Columns("H:H").NumberFormat = "#,##0"
.Columns("I:I").NumberFormat = "0%"
'Setzen der Überschriften
.Range("G1").Value = Year(Date)
.Range("G1").NumberFormat = "0"
.Range("H1").Value = "#"
.Range("I1").Value = "%"
'Färben der Spalte G
With .Columns("G:G").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
'Schleife durchläuft alle Arbeitsblätter und schreibt Umsatz in Firmenübersicht
For Each wks In ActiveWorkbook.Worksheets
'Prüfung ob es sich um Firmenseite handelt
If wks.Name = wks.Range("E1") Then
Set rng = .Columns(6).Find(wks.Name, lookat:=xlWhole)
'Wenn gefunden, dann schreiben der Werte
If Not rng Is Nothing Then
rng.Offset(0, 1) = wks.Range("J3").Value
Else
'Speichern der nicht gefunden Firmen in einer Variablen
strFehler = wks.Name & " / " & strFehler
End If
End If
Next
'Schleife zum Schreiben der Formeln in Spalte H (#) und I (%)
For i = 2 To lngletzteZeile - 2
'Spalte H (#)
.Range("H" & i).Formula = "=G" & i & "-J" & i
'Spalte I (%)
''''''' Diese Formel bekomme ich nicht zum laufen
'''''''Laufzeitfehler 1004
'''''''Originalformel in Zelle I2
'''''''- =WENNFEHLER((G2-J2)/ABS(J2);"")
'''''''mein VBA Versuch
'''''''.Range("I" & i).Formula = "=IFERROR((J" & i & "-M" & i & ")/ABS(M" & i & ");" &  _
Chr(34) & Chr(34) & ")"
Next i
'Schreiben der Werste in letzteZeile (Summe)
.Range("G" & lngletzteZeile).Formula = "=SUM(G2:G" & lngletzteZeile - 2 & ")"
.Range("H" & lngletzteZeile).Formula = "=G" & lngletzteZeile & "-J" & lngletzteZeile
''''Hier die Formel mit ABS . Range("I" & lngletztezeile).Formula = .....
End With
'erneute Schleife durch alle Arbeitsblätter - Anpassen der Umsatzspalten
For Each wks In ActiveWorkbook.Worksheets
'Prüfung ob es sich um Firmenseite handelt
If wks.Name = wks.Range("E1") Then
'Pfüfung ob es bei aktueller Firma Fehler gab
'''' Hier die Abfrage, ob wks.name NICHT in der Variable strFehler vorkommt
'''' oder dieses wks überspringen, wenn es vorkommt
''''Haut nicht hin - If InStr(wks.Name, strFehler) > 0 Then
wks.Range("I3").Value = Year(Date) - 1
wks.Range("I2").Value = Year(Date)
End If
Next
'Wenn Variable strFehler belegt, dann zeigen der Fehler
If (strFehler)  "" Then MsgBox ("Fehler bei folgenden Firmen. Bitte manuell prüfen." & _
Chr(13) & strFehler)
End Sub
Dann habe ich jedoch noch ein logisches Problem auf meiner Übersichtsseite.
Spalte J ist ein Link auf Firmaseite Zelle J3 (Umsatz 16), der durch mein Makro kommt an diese Stelle ja der Umsatz 17.
Gibt es eine Möglichkeit hier anstelle die Formeln durch die absoluten Werte zu ersetzen?
Also die Formel lautet =Firma1J3 / Zellinhalt = 1.450 / dann die Formel entfernen und den Zellinhalt als Wert?
AW: Makro ändert Formel zum Jahreswechsel
28.02.2017 00:38:45
Piet
Hallo Florian
mal ganz spontan, ohne den ganzen Thread und alle Antworten gelesen zu haben. Hast du schon mal:
FomulaLocal ausprobiert?
mfg Piet
AW: Makro ändert Formel zum Jahreswechsel
28.02.2017 08:29:14
Florian
Hallo Piet
Ja, FormulaLocal habe ich auch ausprobiert (dann natürlich mit Wennfehler, statt iferror).
Jedoch mit exakt dem gleichen Ergebnis.
AW: Makro ändert Formel zum Jahreswechsel
28.02.2017 05:44:51
fcs
Hallo Florian,
ich hab dein Makro mal angepasst - in hochgeladener Textdatei
https://www.herber.de/bbs/user/111814.txt
Da du ja ggf. im aktuellen Jahr noch Rechnungen verbuchen willst, die ins Vorjahr gerechnet werden sollen, musst du in den Spalten G und J mit Formeln arbeiten, die die Werte aus J2 bzw. J3 der Kunden-Blätter holen.
Zu Beginn des Makros müssen in Spalte J die Daten des vorletzten Jahres (2018 die von 2016) durch ihre Werte ersezt werden. Danach erfolgt dann das Einfügen der Spalten, Formatieren, und Einfügen der neuen Formeln.
Wenn man Formeln per VBA einfügen will, dann muss man beachten, das bei
Range(ABC).Formula
Range(ABC).FormulaR1C1
das ;(semikolon) in deutschen Formeln durch ein ,(Komma) ersetzt werden muss.
Der Vorteil ist hier, dass der VBA-Code unabhängig von der Sprachversion von Excel is.
Bei
Range(ABC).FormulaLocal
Range(ABC).FormulaR1C1Local
ist die Schreibweise so wie in den Formeln im Tabellenblatt, aber man läuft Gefahr, dass das Makro auf Rechnern mit anderer Sprachversion nicht funktioniert.
Meist kommt man unter VBA mit der R1C1-Schreibweise der Formeln schneller voran.
Anführungszeichen " in Tabellen-Formeln müssen im VBA-Code verdoppelt werden oder man hilft sich mit dem Zeichen 34.
Range(xxx).Formula = "="""""
ist gleichwertig mit
Range(xxx).Formula ="=" & chr(34) & chr(34)
Wenn man die Eingabe der Formeln mit dem Rekorder aufzeichnet, dann bekommt man schon mal die richtige Formelsyntax geliefert als Basis für den erforderlichen VBA-Code
Der aufgezeichnete Code ist dann meist in der R1C1-Schreibweise mit den Funktionsnamen in Englisch.
Man kann unter VBA auch die Formel für einen größeren Bereich festlegen, wenn die Formeln für die Zeilen/Spalten ähnlich sind - im Prinzip so als würde man im Tabellenblatt die Formeln kopieren.
Du musst hier nicht für jede Zeile die Formel in einer For-Next-Schleife einfügen.
LG
Franz
AW: Makro ändert Formel zum Jahreswechsel
28.02.2017 09:13:26
Florian
Wow.
erneut vielen Dank für deine ausführlichen Erläuterungen :)
Zur Formel (auch wenn ich letzlich deine im R1C1 Format genommen habe) das Semikolon war der Überltäter.
Dieses habe ich durch eine Komme ersetzt und meine Formel wurde anstandslos geschluckt.
Aber die Schleife ist nach deiner Lösung natürlich überflüssig.
Also werde ich mir dieses Formelschreiben noch mal ansehen, das könnte meinen anderen Code etwas verkürzen.
Ich habe mir deinen Code stückchenweise in meinen kopiert (und dann meinen durch deinen ersetzt), damit ich die Änderungen besser nachvollziehen kann.
Wenn du erlaubst habe ich noch eine Frage zum Code.
Nach der ersten For Next Schleife hast du die Zeile eingefügt.
strFehler = strSep & strFehler

Und prüfst dann nachher auf strStep und Firmanamen:
If InStr(1, strFehler, strSep & wks.Name & strSep) = 0 Then
Warum ist dies nötig? Kann ich nicht auch einfach auf wks.Name prüfen?
Grund der Frage ist, dass ich ja sonst meine letzte Codezeile leicht abändern muss:
If (strFehler)  "" Then
müsste ja dann zu
If (strFehler)  strStep Then
werden
AW: Makro ändert Formel zum Jahreswechsel
28.02.2017 16:52:45
fcs
Hallo Florian,
Ändere die Zeile nach Next in
If strFehler  "" then strFehler = strSep & strFehler

Dann brauchst du hinten nichts zu ändern.
Es gibt bei der Prüfung der Blattnamen Probleme wenn 2 Blattnamen gleich beginnen.
Zum Beispiel "Firma A" und "Firma ABC".
Deshalb musste ich das Trennzeichen mit in die Prüfungen einbauen.
LG
Franz
AW: Makro ändert Formel zum Jahreswechsel
01.03.2017 08:39:44
Florian
Ja, das macht Sinn.
Alles klar - dann noch einmal vielen Dank für deine Hilfe und auch für die Erklärungen :)
AW: Makro ändert Formel zum Jahreswechsel
01.03.2017 08:56:20
Florian
Guten Morgen
Ich muss nochmal nerven.
Es haut etwas mit den Formeln in der Spalte H nicht hin.
Zeile 2: =G2-4:4 / Zeile 3= G3-5:5.
Das müsste ja = G2-J2 bzw. G3-J3 lauten.
Ich vermute hier ist etwas in der R1C1 Formel verkehrt:
        'Schreiben der Formeln in Spalte H (#)
With .Range(.Cells(2, 8), .Cells(lngletztezeile - 2, 8))
.FormulaR1C1 = "=RC[-1]-R[2]"
End With

AW: Makro ändert Formel zum Jahreswechsel
01.03.2017 11:46:11
Florian
Ha, habe es hinbekommen, da fehlt ein "C" :)

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige