Hallo Werner,
hier mal drei Beispiel-Codes. Da bin ich mal gespannt, was ein Profi dazu sagt. Ich bin ja kein Programmierer sondern Bauingenieur... :-) Tausend Dank und viele Grüße
Private Sub Workbook_Open()
'Ausf?hrung bei ?ffnen der Datei
Call ShortcutAllways
Call SpaltenDeklarieren
End Sub
Option Explicit
'?ffentliche Variablen, die immer vorhanden sein sollen
Public Formatschalter As Integer
Public arrSpalten
Public NameSub As String
Public NameBvh As String
Public Redu As Variant
Public Formatschalter2 As Integer
Sub SpaltenDeklarieren()
'Ermittlung des ArrayBereichs auf Tabelle6
Dim LastRowSpalten As Integer
LastRowSpalten = Tabelle6.Cells(Rows.Count, 6).End(xlUp).Row
'Dem Array die Zeilen-/Reihen-Werte (Variablen f?r weitere Nutzung) aus Spalte zuweisen
arrSpalten = Tabelle6.Range("E2:G" & LastRowSpalten)
'Ermittlung Werte die nicht in Tabelle6 stehen sondern ermittelt werden m?ssen.
arrSpalten(30, 2) = Tabelle1.Cells(Rows.Count, arrSpalten(34, 2)).End(xlUp).Row ' _
Letzte Reihe in Tabelle1
'Korrektur, falls letzte Zeile kleiner sein sollte als erste.
If arrSpalten(30, 2)
Sub ZeilenFormelnEintragen()
'*********************************************************************************************** _
'Formeln l?schen und neu eintragen
'Dies betrifft die Spalten
' Untere Summenzeile l?schen und erneuter Eintragung durch Call-Aufruf der Sub _
KalkSummenzeile()
' Rundungsangaben bei Hinweisen l?schen
' ?berschreibung aller Formeln, die zur Berechnung/Kalkulation "fest" in Zellen eingetragen _
sind:
'EP, GP
'Materialzuschlag
'Anteil Sub/eigene MA
'Sub
'eigene MA
'EK sonstiges
'EK-Kosten bis Rundung/NL/Skonto
' Call-Aufruf der Sub WiederholungPosition()
'*********************************************************************************************** _
Dim i As Integer
'Call SpaltenDeklarieren
Tabelle1.Activate
Formatschalter = 1
'Rundungsangabe bei Hinweisen l?schen l?schen
Range(Cells(arrSpalten(29, 2), arrSpalten(69, 2)), Cells(arrSpalten(30, 2), arrSpalten(69, _
2))).ClearContents
'Formel in einf?gen
'EP
'Range(Cells(arrSpalten(29, 2), arrSpalten(36, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
36, 2))).FormulaLocal = "=WENN(oder(" & arrSpalten(32, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(56, 1) & arrSpalten(29, 2) & "="""");"""";SUMME(" & arrSpalten(58, 1) & arrSpalten(29, 2) & ";" & arrSpalten(60, 1) & arrSpalten(29, 2) & ";" & arrSpalten(61, 1) & arrSpalten(29, 2) & "))"
Range(Cells(arrSpalten(29, 2), arrSpalten(36, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
36, 2))).FormulaLocal = "=WENN(ODER(INDIREKT(Sys!$E$33&ZEILE())="""";INDIREKT(Sys!$E$57&ZEILE())="""");"""";SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE());INDIREKT(Sys!$E$62&ZEILE())))"
'GP
'Range(Cells(arrSpalten(29, 2), arrSpalten(37, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
37, 2))).FormulaLocal = "=WENN(" & arrSpalten(36, 1) & arrSpalten(29, 2) & "="""";"""";" & arrSpalten(32, 1) & arrSpalten(29, 2) & "*" & arrSpalten(36, 1) & arrSpalten(29, 2) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(37, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
37, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$37&ZEILE())="""";"""";INDIREKT(Sys!$E$33&ZEILE())*INDIREKT(Sys!$E$37&ZEILE()))"
'MGKZPro
'Range(Cells(arrSpalten(29, 2), arrSpalten(63, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
63, 2))).FormulaLocal = "=WENN(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";"""";$" & arrSpalten(63, 1) & "$" & arrSpalten(29, 2) - 1 & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(63, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
63, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$44&ZEILE())="""";"""";INDIREKT(Sys!$E$18))"
'MGKZ
'Range(Cells(arrSpalten(29, 2), arrSpalten(44, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
44, 2))).FormulaLocal = "=WENN(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";"""";" & arrSpalten(43, 1) & arrSpalten(29, 2) & "*" & arrSpalten(63, 1) & arrSpalten(29, 2) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(44, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
44, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$44&ZEILE())="""";"""";INDIREKT(Sys!$E$44&ZEILE())*INDIREKT(Sys!$E$64&ZEILE()))"
'Verh?ltnis Sub zu Eigene MA
'Range(Cells(arrSpalten(29, 2), arrSpalten(45, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
45, 2))).FormulaLocal = "=WENN(" & arrSpalten(42, 1) & arrSpalten(29, 2) & """"";" & arrSpalten(22, 1) & ";"""")"
Range(Cells(arrSpalten(29, 2), arrSpalten(45, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
45, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$43&ZEILE())"""";INDIREKT(Sys!$E$23);"""")"
'Lohn und GK Sub
'Range(Cells(arrSpalten(29, 2), arrSpalten(49, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
49, 2))).FormulaLocal = "=WENN(oder(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0);"""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "*(1-" & arrSpalten(45, 1) & arrSpalten(29, 2) & ")*(" & arrSpalten(16, 1) & "/60))"
Range(Cells(arrSpalten(29, 2), arrSpalten(49, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
49, 2))).FormulaLocal = "=WENN(ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0);"""";INDIREKT(Sys!$E$43&ZEILE())*(1-INDIREKT(Sys!$E$46&ZEILE()))*(INDIREKT(Sys!$E$17)/60))"
'Range(Cells(arrSpalten(29, 2), arrSpalten(50, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
50, 2))).FormulaLocal = "=WENN(oder(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0);"""";" & arrSpalten(49, 1) & arrSpalten(29, 2) & "*" & arrSpalten(18, 1) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(50, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
50, 2))).FormulaLocal = "=WENN(ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0);"""";INDIREKT(Sys!$E$50&ZEILE())*INDIREKT(Sys!$E$19))"
'Lohn eigne MA und arrSpalten(52, 2)
'Range(Cells(arrSpalten(29, 2), arrSpalten(51, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
51, 2))).FormulaLocal = "=WENN(oder(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0);"""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "*" & arrSpalten(45, 1) & arrSpalten(29, 2) & "*" & arrSpalten(23, 1) & "+(" & arrSpalten(49, 1) & arrSpalten(29, 2) & "*" & arrSpalten(15, 1) & "))"
Range(Cells(arrSpalten(29, 2), arrSpalten(51, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
51, 2))).FormulaLocal = "=WENN(ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0);"""";INDIREKT(Sys!$E$43&ZEILE())*INDIREKT(Sys!$E$46&ZEILE())*INDIREKT(Sys!$E$24)+(INDIREKT(Sys!$E$50&ZEILE())*INDIREKT(Sys!$E$16)))"
'Range(Cells(arrSpalten(29, 2), arrSpalten(52, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
52, 2))).FormulaLocal = "=WENN(oder(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0);"""";" & arrSpalten(51, 1) & arrSpalten(29, 2) & "*" & arrSpalten(14, 1) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(52, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
52, 2))).FormulaLocal = "=WENN(ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0);"""";INDIREKT(Sys!$E$52&ZEILE())*INDIREKT(Sys!$E$15))"
'Sontige Zuschlag
'Range(Cells(arrSpalten(29, 2), arrSpalten(54, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
54, 2))).FormulaLocal = "=WENN(" & arrSpalten(53, 1) & arrSpalten(29, 2) & "="""";"""";" & arrSpalten(53, 1) & arrSpalten(29, 2) & "*" & arrSpalten(19, 1) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(54, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
54, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$54&ZEILE())="""";"""";INDIREKT(Sys!$E$54&ZEILE())*INDIREKT(Sys!$E$20))"
'Summe Gesamt Einkaufskosten
'Range(Cells(arrSpalten(29, 2), arrSpalten(56, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
56, 2))).FormulaLocal = "=WENN(UND(ODER(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(43, 1) & arrSpalten(29, 2) & "=0);ODER(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0));"""";Summe(" & arrSpalten(43, 1) & arrSpalten(29, 2) & ";" & arrSpalten(51, 1) & arrSpalten(29, 2) & ";" & arrSpalten(49, 1) & arrSpalten(29, 2) & ";" & arrSpalten(53, 1) & arrSpalten(29, 2) & ";" & arrSpalten(55, 1) & arrSpalten(29, 2) & "))"
Range(Cells(arrSpalten(29, 2), arrSpalten(56, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
56, 2))).FormulaLocal = "=WENN(UND(ODER(INDIREKT(Sys!$E$44&ZEILE())="""";INDIREKT(Sys!$E$44&ZEILE())=0);ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0));"""";SUMME(INDIREKT(Sys!$E$44&ZEILE());INDIREKT(Sys!$E$52&ZEILE());INDIREKT(Sys!$E$50&ZEILE());INDIREKT(Sys!$E$54&ZEILE());INDIREKT(Sys!$E$56&ZEILE())))"
'Summe Zuschl?ge
'Range(Cells(arrSpalten(29, 2), arrSpalten(57, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
57, 2))).FormulaLocal = "=WENN(UND(ODER(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(43, 1) & arrSpalten(29, 2) & "=0);ODER(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0));"""";Summe(" & arrSpalten(44, 1) & arrSpalten(29, 2) & ";" & arrSpalten(52, 1) & arrSpalten(29, 2) & ";" & arrSpalten(50, 1) & arrSpalten(29, 2) & ";" & arrSpalten(54, 1) & arrSpalten(29, 2) & "))"
Range(Cells(arrSpalten(29, 2), arrSpalten(57, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
57, 2))).FormulaLocal = "=WENN(UND(ODER(INDIREKT(Sys!$E$44&ZEILE())="""";INDIREKT(Sys!$E$44&ZEILE())=0);ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0));"""";SUMME(INDIREKT(Sys!$E$45&ZEILE());INDIREKT(Sys!$E$53&ZEILE());INDIREKT(Sys!$E$51&ZEILE());INDIREKT(Sys!$E$55&ZEILE())))"
'Summe Einkaufskosten + Summe Zuschl?ge = Mindest-Verkaufspreis
'Range(Cells(arrSpalten(29, 2), arrSpalten(58, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
58, 2))).FormulaLocal = "=WENN(UND(ODER(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(43, 1) & arrSpalten(29, 2) & "=0);ODER(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0));"""";Summe(" & arrSpalten(56, 1) & arrSpalten(29, 2) & ";" & arrSpalten(57, 1) & arrSpalten(29, 2) & "))"
Range(Cells(arrSpalten(29, 2), arrSpalten(58, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
58, 2))).FormulaLocal = "=WENN(UND(ODER(INDIREKT(Sys!$E$44&ZEILE())="""";INDIREKT(Sys!$E$44&ZEILE())=0);ODER(INDIREKT(Sys!$E$43&ZEILE())="""";INDIREKT(Sys!$E$43&ZEILE())=0));"""";SUMME(INDIREKT(Sys!$E$57&ZEILE());INDIREKT(Sys!$E$58&ZEILE())))"
'W+G Prozent
'Range(Cells(arrSpalten(29, 2), arrSpalten(60, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
59, 2))).FormulaLocal = "=WENN(" & arrSpalten(58, 1) & arrSpalten(29, 2) & "="""";"""";" & arrSpalten(20, 1) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(60, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
59, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$59&ZEILE())="""";"""";INDIREKT(Sys!$E$21))"
'Summe W+G Zahl
'Range(Cells(arrSpalten(29, 2), arrSpalten(60, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
60, 2))).FormulaLocal = "=WENN(ODER(UND(ODER(" & arrSpalten(43, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(43, 1) & arrSpalten(29, 2) & "=0);ODER(" & arrSpalten(42, 1) & arrSpalten(29, 2) & "="""";" & arrSpalten(42, 1) & arrSpalten(29, 2) & "=0));" & arrSpalten(58, 1) & arrSpalten(29, 2) & "=0);"""";" & arrSpalten(58, 1) & arrSpalten(29, 2) & "*" & arrSpalten(59, 1) & arrSpalten(29, 2) & ")"
Range(Cells(arrSpalten(29, 2), arrSpalten(60, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
60, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$59&ZEILE())="""";"""";INDIREKT(Sys!$E$59&ZEILE())*INDIREKT(Sys!$E$60&ZEILE()))"
'Summe NL,Skonto,Rundung
'Range(Cells(arrSpalten(29, 2), arrSpalten(61, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
61, 2))).FormulaLocal = "=WENN(" & arrSpalten(58, 1) & arrSpalten(29, 2) & "="""";"""";RUNDEN((SUMME(" & arrSpalten(58, 1) & arrSpalten(29, 2) & ";" & arrSpalten(60, 1) & arrSpalten(29, 2) & "))/(1-(" & arrSpalten(11, 1) & "+" & arrSpalten(10, 1) & "));WENN(" & arrSpalten(69, 1) & arrSpalten(29, 2) & "="""";1;" & arrSpalten(69, 1) & arrSpalten(29, 2) & "))-(SUMME(" & arrSpalten(58, 1) & arrSpalten(29, 2) & ";" & arrSpalten(60, 1) & arrSpalten(29, 2) & ")))"
'Range(Cells(arrSpalten(29, 2), arrSpalten(61, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
61, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$59&ZEILE())="""";"""";RUNDEN((SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE())))/(1-(INDIREKT(Sys!$E$12)+INDIREKT(Sys!$E$11)));WENN(INDIREKT(Sys!$E$70&ZEILE())="""";1;INDIREKT(Sys!$E$70&ZEILE())))-(SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE()))))"
Range(Cells(arrSpalten(29, 2), arrSpalten(61, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
61, 2))).FormulaLocal = "=WENN(INDIREKT(Sys!$E$59&ZEILE())="""";"""";RUNDEN((SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE())))/(1-(INDIREKT(Sys!$E$12)+INDIREKT(Sys!$E$11)));WENN(INDIREKT(Sys!$E$70&ZEILE())="""";1;INDIREKT(Sys!$E$70&ZEILE())))-(SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE())))+WENN(ISTFORMEL(INDIREKT(Sys!$E$37&ZEILE()))=WAHR;0;INDIREKT(Sys!$E$37&ZEILE())-SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE());WENN(INDIREKT(Sys!$E$59&ZEILE())="""";"""";RUNDEN((SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE())))/(1-(INDIREKT(Sys!$E$12)+INDIREKT(Sys!$E$11)));WENN(INDIREKT(Sys!$E$70&ZEILE())="""";1;INDIREKT(Sys!$E$70&ZEILE())))-(SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE())))))))"
'(Legacy)manuKorrektor
'Range(Cells(arrSpalten(29, 2), arrSpalten(67, 2)), Cells(arrSpalten(30, 2), arrSpalten( _
67, 2))).FormulaLocal = "=WENN(ISTFORMEL(INDIREKT(Sys!$E$37&ZEILE()))=WAHR;"""";INDIREKT(Sys!$E$37&ZEILE())-SUMME(INDIREKT(Sys!$E$59&ZEILE());INDIREKT(Sys!$E$61&ZEILE());INDIREKT(Sys!$E$62&ZEILE())))"
'NKST-Verkn?pfung zu Tabelle "VP"
Cells(2, arrSpalten(72, 2)).FormulaLocal = "=VP!W64"
'NKST-Verkn?pfung der Herkunft
Cells(3, arrSpalten(72, 2) - 2).FormulaLocal = "=""(""&WENN(VP!I60=""?berpr?fung _
erforderlich"";VP!I60;WENN(VP!W63=0;""lt. Vorgabe"";WENN(VP!W63=VP!W64;""Sch?tzung"";""lt. Vorg. & Sch?tzung""))&"")"")"
Formatschalter = 0
End Sub