Herbers Excel-Forum - das Archiv

WorksheetFunction.Sumproduct

Bild

Betrifft: WorksheetFunction.Sumproduct
von: Peter

Geschrieben am: 04.01.2007 22:27:43
Guten Abend
Ich möchte mit nachstehendem Code in einer Tabelle in gewissen Zellen das Ergebnis einer Worksheetfunktion (Summe von zwei mal Summenprodukt) einfügen.
Ich habe mal probiert die Formel einzufügen (vgl. unten). Das funktioniert.
Nun wollte ich anstelle der Formel der Wert der Formel eintragen. Da gibt es Probleme mit gewissen Bestandteilen der Formel, zum Beispiel kann er mit den Namen der benannten Bereichen (z.B. _Jahr) nichts anfangen.
Im Moment habe ich dazu zwei Fragen:
Wie setze ich diese Formel mit WorksheetFunction.SumProduct um?
Die erste Formel setze ich auf Zeile 10 ein. Dementsprechend sind auch gewisse Referenzwerte auf Zeile 10 enthalten (z.B. _Segment=$C10). Wie kann ich diese Bezüge variabel gestalten, so dass wenn ich auf Zeile 11 bin dann die Referenz C11 verwendet wird?
Besten Dank für ein Feedback!
Peter
Sub aktualisiere_Segment()
Dim aBook As Workbook
Dim aSheet As Worksheet
Dim zNr As Long
Set aBook = ThisWorkbook
Set aSheet = Worksheets("Segment")
With aSheet
zNr = 10
Do While .Cells(zNr, 2) <> "Total"
If .Cells(zNr, 6) <> "" Then
.Cells(zNr, 6) = "=sumproduct((_Jahr=F$3)*(_Segment=$C10)*(_Konto=$E10)*_Betrag)+sumproduct((_AKonto=$E10)*(_ASegment=$C10)*(_APlanjahr1))"
Else
End If
zNr = zNr + 1
Loop
End With
End Sub

Bild

Betrifft: AW: WorksheetFunction.Sumproduct
von: Luc:-?
Geschrieben am: 04.01.2007 23:06:38
Hallo Peter,
...(_Segment=$C10)......(.Range("_Segment") = .Cells(zNr, 3))... und analog bei den anderen Bereichsnamen.
Gruß Luc :-?
Bild

Betrifft: AW: WorksheetFunction.Sumproduct
von: Peter

Geschrieben am: 05.01.2007 08:29:18
Guten Tag miteinander
Ich habe versucht, wie von Luc aufgezeigt, die Formel in eine Worksheet Function umzusetzen. Vorerst nur die erste sumproduct-Formel.
Irgendwas funktioniert noch nicht, denn die Fehlermeldung "Typen unverträglich" erscheint.
Die bisherige Codezeile habe ich ausgeklammert(?) im untenstehenden Code belassen.
Kann mir jemand sagen, was in meiner WorksheetFunction falsch ist?
Vielen Dank
Peter
Sub aktualisiere_Segment()
Dim aBook As Workbook
Dim aSheet As Worksheet
Dim zNr As Long
Set aBook = ThisWorkbook
Set aSheet = Worksheets("Segment")
With aSheet
zNr = 10
Do While .Cells(zNr, 2) <> "Total"
If .Cells(zNr, 6) <> "" Then
'.Cells(zNr, 6) = "=sumproduct((_Jahr=F$3)*(_Segment=$C10)*(_Konto=$E10)*_Betrag)"
.Cells(zNr, 6).Value = WorksheetFunction.SumProduct((Range("_Jahr") = .Cells(3, 6)) * (Range("_Segment") = .Cells(10, 3)) * (Range("_Konto") = .Cells(10, 5)) * Range("_Betrag"))
Else
End If
zNr = zNr + 1
Loop
Bild

Betrifft: AW: WorksheetFunction.Sumproduct - .Range!
von: Luc:-?
Geschrieben am: 05.01.2007 21:17:48
Hallo Peter,
versuchs mal mit den von mir vorgesehenen Punkten vor Range, sonst bezieht sich das auf das aktuelle Blatt und von dem weiß ich erst recht nicht, ob da was oder was da steht... ;-)
Gruß Luc :-?
Bild

Betrifft: AW: WorksheetFunction.Sumproduct - .Range!
von: Peter

Geschrieben am: 05.01.2007 21:32:00
Hallo Luc
Ja, das habe ich tatsächlich übersehen. Vielen Dank. Anscheinend habe ich etwas Weiteres übersehen. Kann ich Einzelteile im Direktfenster überprüfen (wenn ja, wie?) oder gibt es eine andere Möglichkeit, wie man dem Fehler in diese Codezeile auf die Schlichte kommt?
Noch eine Verständnisfrage zum . vor Range: Bei .Cells steht ja auch ein Punkte vornean, bisher ging ich davon aus, dass mit diesem dokumentiert wird, dass die Zelle in meinem Beispiel in der Tabelle "Segment" steht. Ist das richtig?
Danke und Gruss
Peter
.Cells(zNr, 6) = WorksheetFunction.SumProduct((.Range("_Jahr") = _
.Cells(3, 6)) * (.Range("_Segment") = .Cells(10, 3)) * (.Range("_Konto") = _
.Cells(10, 5)) * .Range("_Betrag"))
Bild

Betrifft: Nachtrag zu Fehlermeldung
von: Peter
Geschrieben am: 05.01.2007 22:04:28
Hallo
Ich muss noch nachtragen, dass die Fehlermeldung nun etwas anders lautet, nämlich:
"Laufzeitfehler '1004': Die Methode 'Range' für das Objekt '_Worksheet' ist fehlgeschlagen
Gruss, Peter
Bild

Betrifft: AW: Nachtrag 2 zu Fehlermeldung
von: Peter

Geschrieben am: 06.01.2007 08:00:53
Guten Tag
Ich habe noch weiter gepröbelt, habe die Lösung jedoch noch nicht gefunden. Hier noch die Bezüge der 4 Ranges, die in der WorksheetFunction.SumProduct angesprochen werden.
Hoffentlich kann mir jemand weiterhelfen.
Danke, Peter
_Betrag =Import_mod!$L$4:$L$10000
_Jahr =Import_mod!$K$4:$K$10000
_Konto =Import_mod!$J$4:$J$10000
_Segment =Import_mod!$C$4:$C$10000
Bild

Betrifft: AW: WorksheetFunction.Sumproduct - .Range!
von: Luc:-?

Geschrieben am: 09.01.2007 00:29:24
Hallo Peter,
war die letzten Tage leider auf Grd einer interessanten Aufgabenstellung verhindert. So, jetzt zu deinen Fragen:
1.Kann ich Einzelteile im Direktfenster überprüfen (wenn ja, wie?) oder gibt es eine andere Möglichkeit, wie man dem Fehler in dieser Codezeile auf die Schlichte kommt?
Ja, da eintragen. Ansonsten unter Debug in Einzelschritten abarbeiten, dazu Haltepunkte setzen. Außerdem können Werte von Ausdrücken angezeigt bzw bei Erreichen 1es best.Wertes angehalten wdn (rechte Maustaste ausprob!).
2.Zum . vor Range: Bei .Cells steht ja auch ein Punkte vornean, bisher ging ich davon aus, dass mit diesem dokumentiert wird, dass die Zelle in meinem Beispiel in der Tabelle "Segment" steht. Ist das richtig? - Ja, wg der With-Klammer. Ging davon aus, dass das hier auch für Range gilt. Ohne . bezieht sich das sonst auf das aktuell ausgewählte Blatt.
3."Laufzeitfehler '1004': Die Methode 'Range' für das Objekt '_Worksheet' ist fehlgeschlagen
_Betrag =Import_mod!$L$4:$L$10000 , _Jahr =Import_mod!$K$4:$K$10000
_Konto =Import_mod!$J$4:$J$10000 , _Segment =Import_mod!$C$4:$C$10000

Ja, dann war Range ohne . wohl theoretisch richtig, wenn Import_mod das aktuelle Blatt ist! U.U. liegt sonst der Fehler wohl in der Formel selbst, vorausgesetzt .Cells liegen tatsächlich auf dem Blatt "Segment".
Scheint doch etwas vertrackt zu sein. ;-)
Viel Glück!
Luc :-?
Bild

Betrifft: AW: WorksheetFunction.Sumproduct - .Range!
von: Peter

Geschrieben am: 09.01.2007 17:58:11
Hallo Luc
Vielen Dank für die Rückmeldung. Ich habe in der Zwischenzeit weiter geforscht und herausgefunden, dass komplexe sumproduct Formeln nicht mit WorksheetFunction umgesetzt werden können.
Fundstellen:
https://www.herber.de/forum/archiv/680to684/t682601.htm
https://www.herber.de/forum/archiv/440to444/t442213.htm (inkl. dort enthaltener Verweis)
Dementsprechend habe ich nun mein Problem mit Evaluate gelöst.
Nun habe ich noch eine Anschlussfrage:
Ich muss die Werte nicht nur nach unten (in einer Spalte abfüllen, sondern auch in weiteren Spalten. Und zwar immer von Spalte 6 bis 20, wenn in Spalte 6 überhaupt ein Eintrag erfolgt. Im hochgeladenen Word-File habe ich die 15 Codezeilen für die einzelnen Spalten reinkopiert.
Kannst du mir allenfalls sagen, wie ich nun noch eine Schlaufe einbaue, dass ich nicht zehn verschiedene Codezeilen brauche? Oder macht es nicht Sinn, den Code so zu komprimieren?
Danke für eine Rückmeldung.
Peter
https://www.herber.de/bbs/user/39522.doc
 Bild