Microsoft Excel

Herbers Excel/VBA-Archiv

mit VBA Array-Formel einsetzen - Fehlermeldung

Betrifft: mit VBA Array-Formel einsetzen - Fehlermeldung von: Peter
Geschrieben am: 01.03.2013 08:31:46

Guten Tag

rDestin ist ein Spaltenbereich und ich möchte mittels VBA eine komplexe Array-Formel (pro Zelle im Spaltenbereich) einsetzen

Die Formel habe ich im Excel eingetragen und funktioniert.
Dann habe ich im Direktbereich die Formel abgefragt und diese so übernommen
Ich setze die Formel in der ersten Zeile des Bereiches ein. In Ausnahmefällen ist der Bereich rDestin nur eine Zelle. Deshalb frage ich anschliessend ab, ob es sich um mehrere Zellen handelt. Wenn ja, fülle ich dann die Formel unten aus.

Nun erhalte ich folgende Fehlermeldung:
"Laufzeitfehler '1004': Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden".

Wo liegt das Problem?

Gruss, Peter

With rDestin
.Cells(1, 1).NumberFormat = "#,##0.00;-#,##0.00;"

.Cells(1, 1).FormulaArray = "=SUMIF(R6C19:R55C19,RC[-86],R6C102:R55C102)" & _
"" & "+SUMPRODUCT((R6C[-86]:R55C[-86]=RC[-86])*(R6C[-71]:R55C[-71])" & _
"" & "*(R6C[-2]:R55C[-2])/IF(R6C[-72]:R55C[-72]=0,1,R6C[-72]:R55C[-72]))" & _
"" & "+SUMPRODUCT((R6C[-86]:R55C[-86]=RC[-86])*(R6C[-69]:R55C[-69])" & _
"" & "*(R6C[-1]:R55C[-1])/IF(R6C[-70]:R55C[-70]=0,1,R6C[-70]:R55C[-70]))"""

If rDestin.Rows.Count > 1 Then .FillDown

End With

  

Betrifft: AW: mit VBA Array-Formel einsetzen - Fehlermeldung von: Klaus M.vdT.
Geschrieben am: 01.03.2013 08:46:17

Hallo Peter,

Array-Formeln kannst du nicht "nach unten ziehen". Versuch das mal in Excel per Hand, da bekommst du die Fehlermeldung "kann Teil eines Array nicht ändern" oder so. Und VBA kann/darf das eben auch nicht.
Statt filldown, hol dir die letzte Zeile und benutze "copy" oder gleich .Range(.(cells(1,1),.cells(letzteZeile,1)).FormulaArray. Ist, nebenbei bemerkt, 100mal schneller als FillDown.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: mit VBA Array-Formel einsetzen - Fehlermeldung von: Peter
Geschrieben am: 01.03.2013 09:42:06

Hallo Klaus
Danke für deine Antwort.
Mein Problem beginnt leider schon etwas früher - und zwar wird die ArrayFormel schon gar nicht eingetragen. Anstelle dessen kommt die von mir erwähnte Fehlermeldung.
Hat jemand sonst noch eine Idee, wo das Problem mit der Matrixformel liegt?

Gruss, Peter


  

Betrifft: Deine Aussage, ... von: Luc:-?
Geschrieben am: 01.03.2013 12:50:19

…Klaus,
Array-Formeln kannst du nicht "nach unten ziehen".
…ist inkorrekt! Sie müssen im Block gezogen wdn! Hier dürfte es sich ohnehin um eine einzellige Matrixformel handeln, die ganz normal gezogen wdn kann. Ob das nun auch mit .FillDown fktionieren würde, käme auf einen Versuch an, ist aber wahrscheinlich.
Allerdings liegt das Problem ja ohnehin woanders. Bei der abgebildeten Fml ist ein Doppel-" am Ende zuviel und die Zeilenanfänge mit "" & schlicht überflüssig. Wenn das im Original auch so ist, könnte das den Fehler erklären.
Gruß Luc :-?


  

Betrifft: AW: Deine Aussage, ... von: Peter
Geschrieben am: 01.03.2013 14:46:27

Hallo Luc
Danke für deine Hinweise.
Es ist so, dass sich einzellige Matrixformeln mit .FillDown nach unten ziehen lassen (ich habe das schon anderweitig so gemacht, deshalb kam ich überhaupt auf die Idee)..

Bei der abgebildeten Formel habe ich das Doppel-"" am Ende eliminiert und auch die Zeilen anfänge mit ""&. Doch es blieb alles beim alten.
Bei der ursprünglich geposteten Formel setzte sich die Array-Formel aus einer SUMIF und zwei SUMPRODUCT-Formeln zusammen. Ich habe die erste SUMIF-Formel nun auch in eine SUMPRODUCT-Formel umgebaut.
Dabei habe ich festgestellt, dass ich diese Array-Formel mittels VBA in der Zeile eintragen kann, wenn ich eine von drei SUMPRODUCT Formeln weglasse. Anscheinend sind 3 Teilformeln zu viel, obwohl ich ja nicht bei 256 Zeichen angekommen bin. Oder liegt doch ein anderes Problem vor?

Meine Formel habe ich wie gesagt in der Excel-Zelle eingegeben und mit ?ActiveCell.FormulaR1C1 abgefragt. Doch mit

With rDestin
.Cells(1,1).FormulaArray = "=SUMPRODUCT((R6C[-86]:R55C[-86]=RC[-86])*(R6C[-3]:R55C[-3]))+SUMPRODUCT((R6C[-86]:R55C[-86]=RC[-86])*(R6C[-71]:R55C[-71])*(R6C[-2]:R55C[-2])/IF(R6C[-72]:R55C[-72]=0,1,R6C[-72]:R55C[-72]))+SUMPRODUCT((R6C[-86]:R55C[-86]=RC[-86])*(R6C[-69]:R55C[-69])*(R6C[-1]:R55C[-1])/IF(R6C[-70]:R55C[-70]=0,1,R6C[-70]:R55C[-70]))"
End With

ist nichts zu machen.

Es wäre ja nett zu wissen, ob und wenn ja, weshalb es so ist, dass sich eine solche Formel nicht eintragen lässt, auch wenn meine Abfrage im Direktberech gerade diese ausgibt.

Gruss, Peter


  

Betrifft: RC[-86] von: Klaus M.vdT.
Geschrieben am: 01.03.2013 10:00:03

Hi,

das Problem ist RC[-86]

dein rDestin kenne ich ja leider nicht. Es sei mal Range("C5:C100")
rDestin.cells(1,1) ist also Zelle C5.
Von C5 aus versuchtst du nun 86 Spalten nach links zu gehen (RC[-86]). Da ist aber nichts.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: RC[-86] von: Peter
Geschrieben am: 01.03.2013 10:08:54

Hallo Klaus
Da du rDestin nicht kennst, ist deine Vermutung naheliegend.
Ich habe jedoch die Formel in der entsprechenden Zelle manuell eingetragen und anschliessend im VBA-Direktbereich abgefragt:
?activecell.FormulaR1C1
- R1C1 Format infolge http://msdn.microsoft.com/de-de/library/office/ff837104.aspx)

?rdestin.Cells.Address
$DA$6:$DA$55

rDestin ist bei mir ein Range in Spalte DA, also Spaltennummer 105 und somit ist RC[-86] Spalte S

Gruss, Peter


  

Betrifft: AW: RC[-86] von: Klaus M.vdT.
Geschrieben am: 01.03.2013 10:29:53

rDestin ist bei mir ein Range in Spalte DA, also Spaltennummer 105 und somit ist RC[-86] Spalte S

Nö. Wenn rDestin bei DA anfängt, ist rDestin.Columns(1) = Spalte 105. Wenn du die Formel manuell einträgst, bezieht sie sich aufs Blatt. Du versuchst jetzt aber, sie aufs rDestin zu beziehen (was nicht klappt).
rDestin.cells(1,1) währ nach deiner Logik ja Zelle A1, ist es aber nicht.

Ich kann dir nicht helfen, da ich deine Tabelle nicht kenne. Bau den Code so um, dass du rDestin (für die Formel) nicht mehr brauchst.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: RC[-86] von: Peter
Geschrieben am: 01.03.2013 10:43:24

Hallo Klaus

zu rDestin

meine Abfragen im Direktbereich zeigen folgendes:

?rDestin.Cells(1,1).address
$DA$6
?rDestin.Cells(1,1).offset(0,-86).address
$S$6

(1,1) bezieht sicha uf rDestin, und da der Bereich in Zeile 6 beginnt, ergibt dies DA6 und dementsprechend RC[-86] S6.

Was mich irritiert: Ich habe in Zelle DA6 eine korrekte Formel. diese auswähle ich aus, gabe dann im VBA-Direktbereich 'activecell.formular1c1 ein. Wenn ich dann die ausgewertete Formel mit VBA einsetzen will, klappt das nicht.

Vielleicht hat noch sonst jemand eine Idee.

Gruss, Peter


 

Beiträge aus den Excel-Beispielen zum Thema "mit VBA Array-Formel einsetzen - Fehlermeldung"