[VBA] Probleme mit Formelzuweisung in Excel


Tarnal

Grünschnabel
Hallo,

ich habe eine Funktion geschrieben, die eine zusammengesezte SUMMEWENN formel in eine Zelle schreiben soll. Nun gibt mir VBA aber als Fehler " '1004' Anwendungs- oder Objektdefinierter Fehler" aus, wenn ich diese Formel als Cells.Formula schreiben möchte.

Die SummeWenn formel scheint in Ordnung zu sein, denn wenn ich sie als Value schreibe und einfach in Excel ein Gleichheitszeichen davorsetze, funktioniert sie einwandfrei.

Code:
Sub fillPVCEU()
    Columns("A:A").Select
    Selection.Font.ColorIndex = 2
For Row = 1 To 1000
If Worksheets("PVC EU").Range("A" & Row).Value <> "" Then
    If Worksheets("PVC EU").Range("A" & Row + 1).Value <> "" Then
        For col = 3 To 12
            cname = Worksheets("PVC EU").Cells(Row, "B").Value
            first = False
            arange = 3
            Do While Worksheets(cname).Cells(arange, "E").Value <> ""
                arange = arange + 1
            Loop


            target = Worksheets("template").Cells(23, col + 5).Value
            formel = "=SUMMEWENN(" & cname & "!E3:E" & arange & ";A" & Row & ";" & cname & "!" & target & "3:" & target & "" & arange & ")"
            Worksheets("PVC EU").Cells(Row, col).Formula = formel


        Next
    End If
End If
Next
End Sub
"=SUMMEWENN(" & cname & "!E3:E" & arange & ";A" & Row & ";" & cname & "!" & target & "3:" & target & "" & arange & ")"

cname = Company Name | wird aus einer Tabelle ausgelesen und beschreibt den Namen eines Worksheets

arange = ende einer Range | diese wird vorher ausgerechnet und ist variable

target = ZielSpalte | diese wird ausgelesen aus einem Template


Bsp.: SUMMEWENN(Kunde!E3:E7;A7;Kunde!G3:G7)

Wie gesagt, die Formel die herauskommt funktioniert in Excel. Aber ich kann sie nicht über VBA als Formel einfügen.

Ich danke Euch schon jetzt für Eure Hilfe.
 

Orakel

Erfahrenes Mitglied
Hi Tarnal,

wenn Du Makrocode verwendest, must Du die englischen Formelausdrücke verwenden und die Semikolons durch Kommatas ersetzen.

Aus blabla.Formula="=SUMMEWENN(Kunde!E3:E7;A7;Kunde!G3:G7)"
wird also blablabla.Formula="=SUMIF(Kunde!E3:E7, A7, Kunde!G3:G7)"

Kleiner Tipp von mir: Zeichne Dir ein Makro auf, dass die Formel in die Zelle schreibt. Dann hast Du die korrekte Schreibweise.

Wenn Du die deutschen Formelbezeichnungen verwendest, kannst Du die Zuweisung evtl. mal mit blabla.Value = formel versuchen. Ist aber sicher nicht im Sinne des Erfinders :)
Gruß
Das Orakel
 
Zuletzt bearbeitet:

Tarnal

Grünschnabel
Hallo Orakel,
danke für die schnelle Antwort. Ich habe beides schon ausprobiert. ob ich nun SUMIF verwende oder es versuche per Value zu schreiben, beides generiert mir den gleichen Fehler.

Code:
target = Worksheets("template").Cells(23, col + 5).Value
            formel = "SUMIF(" & cname & "!E3:E" & arange & ";A" & Row & ";" & cname & "!" & target & "3:" & target & "" & arange & ")"
            Worksheets("PVC EU").Cells(Row, col).Formula = formel
So schreibt er mir natürlich die Formel als zeichenkette in die Zelle. Sobald ich vor die Formel ein gleichheitszeichen setze kommt wieder der Fehler.

Übrigens kommt der fehler nicht beim zusammensetzen der Formel, sondern wirklich erst bei der Zuweisung, also hier :

Worksheets("PVC EU").Cells(Row, col).Formula = formel
 

Orakel

Erfahrenes Mitglied
Hi

bitte noch den zweiten Punkt beachten: Semikolon ";" durch Komma "," ersetzen :)

Code:
target = Worksheets("template").Cells(23, col + 5).Value
formel = "SUMIF(" & cname & "!E3:E" & arange & ",A" & Row & "," & cname & "!" & target & "3:" & target & "" & arange & ")"
Worksheets("PVC EU").Cells(Row, col).Formula = formel

Gruß
Das Orakel