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

VBA - Sumproduct

VBA - Sumproduct
05.10.2013 17:46:39
Dip
Hallo Allerseits!
Gerne würde ich eine Problemstellung basierend auf das Planungsfile (siehe Datei im Anhang) einbringen, in der Hoffnung, dass mir jemand weiterhelfen kann.
https://www.herber.de/bbs/user/87528.xlsx
Es geht darum, die WBS Elemente (z.B. 1.1.1.2.1) in Spalte A, Blatt "Version VBA" in die Spalte A, Blatt "csv" zu kopieren (ohne Duplikate!), und zwar nur die entsprechende ausgewählte Stufe (mit dem Filter würde ich die gewünschte Stufe wählen).
Im Blatt "csv", Zelle B2 würde ich das entsprechende Planungsjahr auswählen.
Die Monate auf Zeile 4 würden sich entsprechend anpassen (Funktion Monatsende()).
In der Zelle D5 dachte ich, ich würde die Lösung mittels der Funktion Summenprodukt lösen. Wie Ihr aber erkennt, kann Excel aufgrund von mangelnder Ressourcen bzw. grosser Datenrange/-kalkulation den Wert nicht berechnen.
Wäre die Umsetzung der Funktion Summenprodukt in VBA möglich?
Nachfolgend meine bescheidende Lösungmöglichkeit,
Sub test_sumproduct()
Worksheets("csv").Range("D5:O10").FormulaR1C1 = _
Evaluate("=SUMPRODUCT((R[-1]C=Version_VBA!R3C6:R3C303)*(RC[-3]=Version_VBA!C1)*(Version_VBA! _
_
_
C6:C303))")
End Sub

leider erhalte ich in der Zelle die Fehlermeldung #WERT!...
Nachdem der VBA Code die Summenprodukte kalkuliert hat, sollte man das Blatt "csv" als csv Datei abspeichern können, mit einem bestimmten Dateiname (als Konstante festgelegt) + das Jahr des gewählten Geschäftsjahr, z.B. "Planung 2013.csv"
Somit sollte es ein Steuerelement für das Kopieren der WBS Elemente sowie Summieren deren Werte und ein Steuerelement für die Erstellung der csv. Datei geben.
Das Festlegen von Konstanten im Blatt "Version_VBA" für
Zeile mit den Monaten
Zeile mit 1. Projekt
Eingabespalte 1. Monat
Wertespalte der WBS Elemente
und im Blatt "csv" für
Eingabespalte für WBS Elemente
Zeile mit den Monaten
1. Zeile mit WBS
Eingabespalte 1. Monat
wäre sehr benutzerfreundlich und elegant
Über irgendwelche Tipps/Hilfe würde ich mich sehr freuen!
Beste Grüsse
Dip

1
Beitrag zum Forumthread
Beitrag zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - Sumproduct
07.10.2013 15:28:36
fcs
Hallo Dip,
bevor ich damit anfange irgendetwas an VBA-Code zu generieren ein paar Anmerkungen/Fragen:
1. Die Funktion SUMPRODUCT (SUMMENPRODUKT) funktioniert hier schon.
Allerdings dürfen in dem zu summierenden Bereich keine Zellen mit Texten vorkommen.
Folgende Formel in CSV Zelle D5 funktioniert:
=SUMMENPRODUKT((D$4='Version VBA'!$F$2:$KR$2)*
(csv!$A5='Version VBA'!$A$3:$A$500)*('Version VBA'!$F$3:$KR$500))
oder englisch in R1C1-Schreibweise
=SUMPRODUCT((R4C='Version VBA'!R2C6:R2C304)*
(csv!RC1='Version VBA'!R3C1:R500C1)*('Version VBA'!R3C6:R500C304))
Außerdem müssen die Datumswerte in "Version_VBA" Zeile 2 und "CSV" Zeile 4 identisch ermittelt werden. Also z.B. immer der letzte Tag des Monats.
Die Funktion SUMMENPRODUKT ist bei entsprechender Datenmenge/Anzahl der Prüfungen nicht so schnell. Jedoch sind die Tabellenfunktionen meist schneller als vergleichbare VBA-Lösungen, so dass es hier am einfachsten ist im Blatt "csv" die Formeln im erforderlichen Bereich per Makro einzufügen und anschließend durch ihre Werte zu ersetzen.
2. Doppelte WBS-Nummern
Kommen in Spalte A von Blatt "Version_VBA" tätsächlich WBS-Nummern doppelt vor?
In deinem Beispiel ja nicht.
Falls nein, dann könnte man mit den wesentlich schnelleren Funktionen INDEX und VERGLEICH arbeiten um das Blatt CSV mit Daten zu füllen.
3. Verwaltung von bestimmten Spalten und Zeilennummern in den Blättern über Konstanten.
Für das Blatt Version_VBA hast du sie ja schon. Diese kann man für dieses Teilprojekt natürlich auch verwenden, wenn der Code ins gleiche Modul eingefügt wird oder die Konstanten Public gemacht werden.
4. Verzeichnis für die CSV Dateien
Für den Dateinamen hast du ja schon eine Vorgabe: "Text " & Jahr & ".csv"
Für das Verzeichnis muß auch eine Vorgabe erfolgen. z.B. das gleiche wie die Arbeitsmappe oder auch ein bestimmtes Unterverzeichnis davon.
5. Erstellen der csv-Datei
Dazu wird das komplette Blatt in eine neue Arbeitsmappe kopiert und dann diese Arbeitsmappe als CSV gespeichert.
Sub Make_CSV()
Dim strFileName As String
strFileName = ActiveWorkbook.Path & Application.PathSeparator _
& "Planung " & ActiveWorkbook.Worksheets("csv").Range("B2").Text
Sheets("csv").Copy
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlCSV, CreateBackup:=False, _
Local:=True
Application.DisplayAlerts = False
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = True
End Sub
Gruß
Franz
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige