Microsoft Excel

Herbers Excel/VBA-Archiv

ArrayFormel via VBA einfügen

Betrifft: ArrayFormel via VBA einfügen von: Chris
Geschrieben am: 20.11.2014 16:57:21

Hallo Leute,

ich habe gerade ein Makro aufgezeichnet und würde dieses nun gerne nochmal ausführen. Aber sobald ich das versuche kommt der Fehler: "Die Formel Array Eigenschaft des Range-Objektes kann nicht festgelegt werden."

Waran kann das leigen?? Das Makro habe ich doch genau so aufgezeichnet?!
Dh Die Formel funktioniert im Excel genau so...

Hier der Code:


Sub test6()
' test6 Makro
    Range("G24").Select
    ActiveCell.FormulaR1C1 = ""
    Range("G24").Select
    Selection.FormulaArray = _
        "=IF(ROW('OpRisk Reference Data'!R[-23]C[-6])>SUM(COUNTIF('OpRisk Reference Data'!C[5],{ _
 _
11;12})), """",INDEX('OpRisk Reference Data'!C[-6],SMALL(IF('OpRisk Reference Data'!C[5]={""11"" _
,""12""},ROW('OpRisk Reference Data'!C[-6])), ROW('OpRisk Reference Data'!R[-23]C[-6]))))"
    Range("G24").Select
End Sub

Danke schon mal
Chris

  

Betrifft: kannst Du die Formel aus der Zelle zeigen? oT. von: Tino
Geschrieben am: 20.11.2014 17:03:59




  

Betrifft: AW: kannst Du die Formel aus der Zelle zeigen? oT. von: Chris
Geschrieben am: 20.11.2014 17:07:44

In der Zelle sieht sie so aus...

=WENN(ZEILE('OpRisk Reference Data'!A1)>SUMME(ZÄHLENWENN('OpRisk Reference Data'!L:L;{11;21})); ""; INDEX('OpRisk Reference Data'!A:A;KKLEINSTE(WENN('OpRisk Reference Data'!L:L={"11"."21"}; ZEILE('OpRisk Reference Data'!A:A)); ZEILE('OpRisk Reference Data'!A1)))) 



  

Betrifft: String für die Formel zu lang >255 Zeichen von: Tino
Geschrieben am: 20.11.2014 17:45:37

Hallo,
habe mal die relativen Bezüge durch absolute ersetzt, dadurch wird er etwas kürzer damit es reicht.

Dim sFormel$

sFormel = _
        "=IF(ROW('OpRisk Reference Data'!R1C1)>SUM(COUNTIF('OpRisk Reference Data'!C[5],{11;21}))," & _
            """"", INDEX('OpRisk Reference Data'!C[-6],SMALL(IF('OpRisk Reference Data'!C[5]={""11"",""21""}," & _
            "ROW('OpRisk Reference Data'!C1)),ROW('OpRisk Reference Data'!R1C1))))"
        
Range("G24").FormulaArray = sFormel
Gruß Tino


  

Betrifft: AW: String für die Formel zu lang >255 Zeichen von: Daniel
Geschrieben am: 21.11.2014 09:49:42

Hi
ein weitere Trick um formeln für die eingabe zu verkürzen ist folgender:
1. Benenne die verwendeten Tabellenblätter um mit kurzen Namen
2. gib die Formel mit den kurzen Namen ein
3. ändere die Namen auf den alten Wert. Excel passt automatisch die verwendeten Formeln an.

Sheets("OpRisk Reference Data").Name = "ORD"
sFormel = "=If(Row(ORD!R1C1)>SUM(CountIf(ORD!C[5],...)"
Range("G24").FormulaArray = sFormel
Sheets("ORD").Name = "OpRisk Reference Data"
Gruß Daniel


  

Betrifft: AW: String für die Formel zu lang >255 Zeichen von: Beverly
Geschrieben am: 21.11.2014 10:29:34

Hi Daniel,

genau das steht schon in meinem Beitrag ;-)


GrußformelBeverly's Excel - Inn


  

Betrifft: AW: ArrayFormel via VBA einfügen von: Beverly
Geschrieben am: 20.11.2014 18:03:31

Hi Chris,

benenne zuerst das Tabellenblatt um, trage dann die Formel ein und benenne es wieder zurück:

With Worksheets("OpRisk Reference Data")
    .Name = "OpRisk"
    .Range("G24").FormulaArray = "=IF(ROW('OpRisk'!A1)>SUM(COUNTIF('OpRisk'!L:L,{11;21})),""""," _
 & _
        "INDEX('OpRisk'!A:A,SMALL(IF('OpRisk'!L:L={""11"",""21""}, ROW('OpRisk'!A:A))," & _
        "ROW('OpRisk'!A1))))"
    .Name = "OpRisk Reference Data"
End With

GrußformelBeverly's Excel - Inn


  

Betrifft: gute Idee den Tabellennamen zu kürzen oT. von: Tino
Geschrieben am: 20.11.2014 18:50:17




  

Betrifft: AW: gute Idee den Tabellennamen zu kürzen oT. von: Chris
Geschrieben am: 20.11.2014 18:55:14

Hallo ihr beiden,
danke für euren Hinweis auf die zu lange Formel. Das ist ja sau bescheuert, dass Excel das nicht packt :(
Die Option mit den absoluten Zellbezügen war bei mir nicht möglich, da ich danach noch mit Autofill weitermachen will.

Das mit dem umbenennen ist wirklich eine gute Idee, wusste gar nicht, dass das auch so einfach geht in VBA. Da ich aber noch viele dieser Formeln habe, meine ich dass darunter die Performance sehr leiden wird. Daher habe ich mein Tabellenblatt einfach in RefData umbennant. Das tuts auch :)

Nochmal vielen Dank
Man lernt hier wirklich jede MINUTE was Neues!!!


  

Betrifft: AW: gute Idee den Tabellennamen zu kürzen oT. von: Chris
Geschrieben am: 20.11.2014 18:55:17

Hallo ihr beiden,
danke für euren Hinweis auf die zu lange Formel. Das ist ja sau bescheuert, dass Excel das nicht packt :(
Die Option mit den absoluten Zellbezügen war bei mir nicht möglich, da ich danach noch mit Autofill weitermachen will.

Das mit dem umbenennen ist wirklich eine gute Idee, wusste gar nicht, dass das auch so einfach geht in VBA. Da ich aber noch viele dieser Formeln habe, meine ich dass darunter die Performance sehr leiden wird. Daher habe ich mein Tabellenblatt einfach in RefData umbennant. Das tuts auch :)

Nochmal vielen Dank
Man lernt hier wirklich jede MINUTE was Neues!!!


  

Betrifft: AW: Noch ein Trick für lange Formeln von: Daniel
Geschrieben am: 21.11.2014 14:26:12

Es gib noch eine Möglichkeit, überlange Formeln per VBA nach Excel zu schreiben:

1. Schreibe die Formel als normalen Text in die Zelle, das ist der Fall, wenn der übertragene Wert nicht mit "=" beginnt.
Als Text kannst du wesentlich längere Inhalte in die Zelle schreiben als als Formel:

Range("A1").Value = "xxx=Hier dann die weiter Formel"
2. Ersetze per .Replace das "xxx" durch nichts.
das Ersetzen ist für Excel wie eine Neueingabe, dh es prüft, was da eingeben wurde und erkennt an dem "=" am Anfang, dass es ein Formel sein soll und behandelt die Eingabe entsprechend.
Da jetzt Excel diese Umwandlung in eine Formel macht, gilt die knappe Beschränkung für die Länge von Formeln beim Übertrag von VBA nach Excel nicht, sodern nur das normale Excellimit, welches wesentlich grösser ist.
Damit die Umwandlung in eine Formel funktioniert, musst du für den Text die .Formula-Schreibweise verwenden (A1-Bezüge, englische Schreibweise)

Klappt zumindest für normale Formeln, mit Matrixformeln so direkt nicht.

Gruß Daniel


  

Betrifft: Bei MatrixFormeln könnte man sich mit ... von: Luc:-?
Geschrieben am: 21.11.2014 19:06:37

…dieser Methode behelfen:

Sub MxFmlText()
    With ActiveWindow.RangeSelection.Cells(1)
        .Value = "=" & .Value
    End With
    SendKeys "^,^+~"
End Sub
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: fehlt da nicht noch Select auf die Formelzelle? oT von: Tino
Geschrieben am: 21.11.2014 19:21:02




  

Betrifft: Ja, aber auf alle für die MxFml vorgesehenen ... von: Luc:-?
Geschrieben am: 21.11.2014 19:42:45

…Zellen, Tino;
die hatte ich beim Test manuell ausgewählt. Da hier ja auch das Eintragen des FmlTextes (und die anschld Erweiterung auf den vorgesehenen MxFmlBereich) fehlt, bin ich offensichtlich davon ausgegangen, dass das das HptPgm erledigt, in dem dann in diesem Fall die dafür relevanten CodeZeilen nur ergänzt wdn müssten…
Gruß, Luc :-?


  

Betrifft: ist ja nur wenn einer diesen Beitrag ließt... von: Tino
Geschrieben am: 21.11.2014 19:48:53

Hallo,
würde dieser Vorschlag ohne select nicht funzen.

Gruß Tino


  

Betrifft: Richtig! Ist so vollständiger... ;-) owT von: Luc:-?
Geschrieben am: 21.11.2014 20:50:33

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "ArrayFormel via VBA einfügen"