Matrix-Formel in FormulaR1C1 umwandeln

Bild

Betrifft: Matrix-Formel in FormulaR1C1 umwandeln
von: jockel
Geschrieben am: 11.08.2015 09:50:09

Hallo zusammen,
ich habe eine Tabelle und möchte mit einem zweifachen Sverweis aus einem anderen Sheet Daten holen, wenn ZWEI Kriterien übereinstimmen. Formel sieht so aus:
Zeile2:
{=INDEX(LSZ!$C$2:$C$100;VERGLEICH(S3&T3;LSZ!A$2:A$100&LSZ!B$2:B$100;0))}
Also eine Matrixformel.
Aus Mangel an Kenntnissen wollte ich nun die Formel in FormulaR1C1 umwandeln und per VBA nach unten ziehen. VBA würde dann so aussehen:

Sub LSZ_LSZ_ERG()
'Letzte Zeile ermitteln
    letztezeile = Sheets("tool").UsedRange.SpecialCells(xlCellTypeLastCell).Row
        Worksheets("tool").Range("U2:U" & letztezeile).FormulaR1C1 = "=INDEX(LSZ!R2C3:R100C3, _
MATCH(RC[-2]&RC[-1],LSZ!R2C[-20]:R100C[-20]&LSZ!R2C[-19]:R100C[-19],0))"
        
End Sub

Wenn ich die

Sub ausführe, wird die Formel zwar in die Zeile zwei eingefügt, nach unten gezogen, aber NICHT  _
als MATRIX-Formel.
Funktioniert diese Vorgehensweise nicht mit einem Matrix-Formel ?
Hat jemand vielleicht eine bessere VBA Alternative ?
Danke
Gruß
Jockel

Bild

Betrifft: .FormulaArray statt .FormulaR1C1 (owT)
von: EtoPHG
Geschrieben am: 11.08.2015 10:11:56


Bild

Betrifft: AW: .FormulaArray statt .FormulaR1C1
von: jockel
Geschrieben am: 11.08.2015 13:28:24
Hallo,
leider komme ich da im Moment genau so klar, wie mit meiner jetzigen. Gar nicht :-(
Gibt es noch ne andere Variante, das mit VBA zu lösen ?
Danke
Jockel

Bild

Betrifft: In .FormulaArray die R1C1-Fml eintragen, ...
von: Luc:-?
Geschrieben am: 11.08.2015 16:20:17
…Jockel;
außerdem müssen sich die relativen Angaben auf die 1.Zelle des MatrixFml-Bereichs beziehen, was nicht der Fall zu sein scheint. Man kann eine Fml in Original-US-A1-Notation auch mit Application.ConvertFormula in eine R1C1-Fml umwandeln.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: In .FormulaArray die R1C1-Fml eintragen, ...
von: Jockel
Geschrieben am: 12.08.2015 09:12:08
Hallo Luc, und alle anderen,
aber dann stehe ich doch vor dem gleichen Problem, oder ??
Eigentlich wollte ich ganz weg von dem umwandeln und das so mit VBA lösen.
Geht das nicht ?
GRuß
Jockel

Bild

Betrifft: Na, dann mach's doch, ...
von: Luc:-?
Geschrieben am: 12.08.2015 09:47:13
…Jockel! ;-)
Dein Problem schien aber zu sein, dass die Fml nach (manueller) Umwandlung nicht fktioniert hat, was an falschen relativen R1C1-Bezügen liegen wird. Lass dir danach .FormulaLocal anzeigen bzw schau dir die ins Blatt eingetragene Fml an und du wirst sehen, was wirklich rausgekommen ist.
Alles Andere, also eine reine VBA-Lösung ohne Fml, musst du dir erarbeiten. Allerdings gibt's noch die Möglichkeit mit US-Notation der Fml als Text (einer Variablen) und vbFkt Evaluate.
Übrigens, eine mehrzellige MatrixFml kannst du einmalig .FormulaArray des ganzen Ergebnisbereichs zuordnen, eine 1zellige nur jeder Zelle separat.
Gruß, Luc :-?

Bild

Betrifft: AW: Na, dann mach's doch, ...
von: Jockel
Geschrieben am: 12.08.2015 11:39:59
Hallo Luc,
die Formel mit den Bezügen passt und funktioniert.

Das Problem dabei ist aber, wenn ich die Matrix-Formel in R1C1 umwandle, sie dann per VBA in der Spalte eintrag und das ganze per VBA , sagen wir mal bis Zeile 30 , herunter ziehen lasse, wird die Matrix-Formel als einfache Formel eingetragen, die geschweiften Klammern sind dann weg.
Ich müsste dann jede Zelle, wo die Formel drin steht, manuell anfassen und jede Formel mit STRG/SHIFT/ENTER einzeln abschliessen.
Es geht darum, wie ich es schaffe, dass die Formel als MATRIX-Fomel per VBA eingefügt werden kann.
Das nur noch mal zum Verständnis.
Danke
GRuß
Jockel

Bild

Betrifft: Wenn du eine MatrixFml willst, muss sie eben, ...
von: Luc:-?
Geschrieben am: 12.08.2015 14:45:20
…wie schon ganz am Anfang gesagt, .FormulaArray der jeweiligen ErgebnisZelle (oder ggf aller Zellen) zugeordnet wdn, Jockel;
je nachdem, ob 1- oder mehrzellig muss sie per Schleife oder für den ganzen dazugehörigen Ergebnisbereich eingetragen wdn. Sind das im letzten Fall auch mehrere, muss der Zähler (die LaufVariable der Schleife) dem Rechnung tragen.
Luc :-?

Bild

Betrifft: AW: Wenn du eine MatrixFml willst, muss sie eben, ...
von: jockel
Geschrieben am: 12.08.2015 18:09:20
Hallo,
ich WILL doch keine Matrix-Formel. Das ist doch mein Problem.
Ich wollte doch davon weg kommen.
Das war halt nur die einzige Möglichkeit, die ich im Internet für mein Problem gefunden hsbe.
Gruß Jockel

Bild

Betrifft: AW: Matrix-Formel in FormulaR1C1 umwandeln
von: Daniel
Geschrieben am: 13.08.2015 15:20:16
Hi
wennd du keine Matrixformel willst, kannst du mit einer Hilfsspalte arbeiten.
füge in der Tabelle, die du durchsuchst die beiden Suchbegriffe in einer Hilfsspalte zu einem Wert zusammen (z.B. durch Verketten mit "&")
dh in die Tabelel LSZ kommt in die Spalte D die Formel: =A2&B2
dann kannst du mit dem einfachen Index und Vergleiche suchen: =Index(LSZ!C:C;Vergleich(S3&T3;LSZ!D:D;0))
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Matrix-Formel in FormulaR1C1 umwandeln"