Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Variable Quellen in Sumproduct VBA

Variable Quellen in Sumproduct VBA
16.01.2019 17:29:30
Alex
Hallo,
bräuchte eure Hilfe. Ich möchte diese Formel
=INDEX(MyTable!$A$1:$AZ$2000;SUMMENPRODUKT((MyTable!$A$1:$AZ$2000=$A2)*ZEILE(MyTable!$1:$2000)); _
_
SUMMENPRODUKT((MyTable!$A$1:$AZ$2000="Kommentar")*SPALTE(MyTable!$A$1:$AZ$2000)))

mit Variablen in VBA haben. Diese Formel funktioniert auch. In VBA wird jede Zelle im gefilterten Bereich angepesprochen. Meine VBA Version der Formel lautet wie folgt:
 rngArea.Cells(lngRow, 1).Offset(0, 24).Formula = "=INDEX([" & _
filename & "]MyTable!A1:AZ2000;SUMPRODUCT(([" & filename & "]MyTable!A1:AZ2000=" & _
rngArea.Cells(lngRow, 1) & ")*ROW([" & filename & "]MyTable!1:2000)); _
SUMPRODUCT(([" & filename & "]MyTable!A1:AZ10=""Kommentar"")* _
COLUMN([" & filename & "]MyTable!A1:AZ10)))"

Wo ist mein Denkfehler? (Die Umbrüche habe ich manuell hier eingetragen)
Die Schleife bzw. der Code funktioniert, wie es soll, nur die Formel nicht.

For i = 0 To UBound(KSwb, 1)
filename = KSwb(i)
If filename  "" Then
Set KSwb(i) = Workbooks.Open(TWBPfad & "\" & filename, UpdateLinks:=0)
Sheets(1).Name = "MyTable"
ThisWorkbook.Activate
Sheets(1).Activate
ersteZeile.AutoFilter Field:=1, Criteria1:= _
"*" & CC(i) & "*"
With Sheets(1).UsedRange
Set rngFilter = .Offset(2, 1).Resize(.Rows.Count - 2, 1) _
.SpecialCells(xlCellTypeVisible)
End With
For Each rngArea In rngFilter
For lngRow = 1 To rngArea.Rows.Count
rngArea.Cells(lngRow, 1).Offset(0, 24).Formula = "=INDEX([" & _
filename & "]MyTable!A1:AZ2000;SUMPRODUCT(([" & filename & "]MyTable!A1:AZ2000=" &_
rngArea.Cells(lngRow, 1) & ")*ROW([" & filename & "]MyTable!1:2000)); _
SUMPRODUCT(([" & filename & "]MyTable!A1:AZ10=""Kommentar"")*COLUMN([" & filename & "]MyTable! _
A1:AZ10)))"
Next lngRow
Next rngArea
Else
MsgBox "Die Datei " & CC(i) & " wurde nicht gefunden"
End If
Next i
Vielen Dank

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Variable Quellen in Sumproduct VBA
16.01.2019 17:53:23
Daniel
Hi
bei .Formula musst du die Formel amerikanisch schreiben, dh nicht nur englischen Funktionsnamen, sondern auch das KOMMA als Parametertrennzeichen und bei Dezimalzahlen den Punkt.
du kannst auch die deutsche Schreibweise verwenden, wenn du die Formel mit .ForumlaLocal = "=.." in die Zelle schreibst.
(dann könntest du den Formeltext direkt aus der Zelle kopieren)
wenn du Variablen in der Formel haben willst, würde ich so vorgehen:
1. schreibe die Formel in eine Stringvariable. Schreibe hierbei überall dort wo eine Variable eingesetzt werden soll, einen DummyText hin.
2. ersetzte in der Variablen den DummyText durch den entsprechenden Variablenwert:
Dim FO as string
FO = "=INDEX('[_xxx_]MyTable'!$A$1:$AZ$2000;SUMMENPRODUKT(('[_xxx_]MyTable'!$...)"
FO = Replace(FO, "_xxx_", filename)
Cells(...).FormulaLocal = FO
das hat den Vorteil, dass du die Formel im Quellcode als zusammenhängenden Text schreiben kannst und somit wesentlich übersichtlicher ist, und du besser überprüfen kannst, ob der Formeltext richtig ist.
außerdem kannst du bei längeren Formeln den Text in mehrere Happen aufteilen:
FO = "=Teilformel1"
FO = FO & "Teilformel2"
FO = FO & "Teilformel3"
usw

und vermeidest damit den VBA-Zeilenumbruch mit "_", welcher bei Texten das ganze zusätzlichz verkompliziert, weil der "_" nicht mitten im Text gesetzt werden kann und du erst den Teiltext beenden musst und nicht vergessen darfst, den nächsten Teiltext mit "&" anzubinden.
außerdem sollest du bei variablen Datei- oder auch Blattnamen immer die Hochkommas verwenden.
diese werden benötigt, wenn ein Sonderzeichen im Blatt- oder Dateinamen vorkommt und hierzu zählt schon das Leerzeichen.
gibst du die Formel von Hand ein, ergänzt Excel die Hochkommas automatisch, schreibst du die Formel per Code, muss man sie selber einfügen.
Gruß Daniel
Anzeige
AW: Variable Quellen in Sumproduct VBA
16.01.2019 20:34:23
Alex
Vielen Dank, läuft jetzt. Allerdings hatte ich Probleme den Range zu ersetzen. Da stand immer Objekt nicht festgelegt. Dann habe ich 2 " dazu geschrieben und es lief (dachte damit macht man nur ""texte"")
rngArea.Cells(lngRow, 1)
.
Wie kann ich diese Zeile ersetzen lassen, wie bei der langen Formel?
FoIn = "=INDEX('[_XXX_]MyTable'!A1:AZ2000,SUMPRODUCT(('[_XXX_]MyTable'!A1:AZ2000="" & rngArea. _
Cells(lngRow, 1) & "")*ROW('[_XXX_]MyTable'!1:2000)),SUMPRODUCT(('[_XXX_]MyTable'!A1:AZ10=""Kommentar"")*_
COLUMN('[_XXX_]MyTable'!A1:AZ10)))"
FoIn = Replace(FoIn, "_XXX_", filename)

Anzeige
AW: Variable Quellen in Sumproduct VBA
16.01.2019 21:21:49
Alex
habs gelöst! Danke dir sehr!
   FoIn = "=INDEX('[_XXX_]MyTable'!A1:AZ2000,SUMPRODUCT(('[_XXX_]MyTable'!A1:AZ2000=""BezZel"")* _
ROW('[_XXX_]MyTable'!1:2000)),SUMPRODUCT(('[_XXX_]MyTable'!A1:AZ10=""Kommentar"")*COLUMN _
('[_XXX_]MyTable'!A1:AZ10)))"
FoIn = Replace(FoIn, "BezZel", " & rngArea.Cells(lngRow, 1) & ")
FoIn = Replace(FoIn, "_XXX_", filename)

322 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige