Letzte Zeile in Spalte in Matrix-Formel einbauen

Bild

Betrifft: Letzte Zeile in Spalte in Matrix-Formel einbauen
von: Malanda
Geschrieben am: 02.10.2015 12:02:46

Hallo,
ich arbeite gerade an einem etwas umfangreicheren Projekt für die Buchhaltung, wo ich gerne eine Übersicht über alle Lieferanten bekommen würde und die jeweiligen Ankäufe die in einem bestimmten Zeitraum getätigt wurden. Dafür brauch ich zunächst eine reine Übersicht aller Lieferanten, die es gab und habe mir diese Formel zusammen geschustert und mit VBA recorded.

Selection.FormulaArray = _
"=IF(SUM(COUNTIF('Transfer Vendors'!R2C7:R50C7,R2C1:R[-1]C))>=SUM(('Transfer Vendors'!R2C7:R50C7<>"""")*1), """",INDEX('Transfer Vendors'!R2C7:R50C7,MATCH(1,(COUNTIF(R2C1:R[-1]C,'Transfer Vendors'!R2C7:R50C7)=0)*('Transfer Vendors'!R2C7:R50C7<>""""), 0)))"
Selection.AutoFill Destination:=Range("A3:A60"), Type:=xlFillDefault

DAS PROBLEM IST FOLGENDES: Ich würde gerne die Zeilenanzahl bis zum letzten Eintrag automatisch bestimmen lassen und in der Formel verankern, da die Tabelle mit den gesuchten Werten sich ja stets ändert und dementsprechend auch in der Länge variiert. Bisher habe ich das über eine Variable gelöst.
Var1 = Sheets("Transfer Vendors").Range("G100000").End(xlUp).Row

Aber ich weiß nicht wie ich den Wert jetzt in meiner Formel verankern könnte.
Könnt ihr mir helfen?
Beste Grüße
Malanda

Bild

Betrifft: AW: Letzte Zeile in Spalte in Matrix-Formel einbauen
von: Daniel
Geschrieben am: 02.10.2015 12:13:49
Hi
gibt zwei Möglichkeiten:
den Formeltext and der Stelle, an der die Variable eingebunden werden soll unterbrechen und mit der Variable über das &-Symbol verketten:
...FormulaArray = "=IF(.....R2C:R" & var1 & "C7,....")
das finde ich aber unübersichlich, weil ich dann den Formeltext nicht mehr so einfach als ganzes lesen kann (vorallem dann, wenn noch ein Fehler drinsteckt, den man finden muss)
daher mache ich es so, dass ich den Formeltext erstmal in eine Variable schreiben und an der stelle, an der der Variablenwert eingefügt werden soll, einen Dummy-Zeichenfolge einsetze, welche ansonsten in der Formel nie vorkommt.
Dann ersetze in in diesem Text die Dummy-Zeichenfolge durch den variablenwert.
ist zwar etwas aufwendiger, aber viel übersichtlicher (vorallem, wenn die Variable mehrfach in die Formel übernommen werden muss:

strFO = "=IF(.....R2C:RxxxC7,....")
strFO = Replace(strFO, "xxx", Var1)
...FormulaArray = strFO

Gruß Daniel

Bild

Betrifft: AW: Letzte Zeile in Spalte in Matrix-Formel einbauen
von: Malanda
Geschrieben am: 02.10.2015 17:32:45
Hej Daniel,
vielen Dank für die schnelle und kompetente Antwort. ich hatte immer das zweite "&" Zeichen nach der variabel vergessen. Daher konnte es auch nicht funktionieren. Ich kann mir vorstellen, dass deine zweite Lösung geschmeidiger aussieht aber muss ehrlich sagen, dass ich es nicht hundertprozentig verstanden habe. Also speziell wofür strFO steht und warum FormulaArray = StrFO am Ende ist. Das muss ich mir wohl noch einmal in einer ruhigen Minute anschauen.

Bild

Betrifft: AW: Letzte Zeile in Spalte in Matrix-Formel einbauen
von: Daniel
Geschrieben am: 02.10.2015 17:42:51
Hi
naja, normales Arbeiten mit Variablen halt.
wenn man einen festen Textwert (so wie den Formeltext) mehrfach im Code benötigt, schreibt man ihn lieber in eine Variable und verwendet im Code die Variable statt dem festen Text.
Also nichts anderes, als du mit deiner Var1 anstelle der Zeilennummer der letzten Zeile auch machst.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Letzte Zeile in Spalte in Matrix-Formel einbauen"