Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 08:08:24
Pivot Vormonatsveränderung
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
936to940
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
936to940
936to940
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrix-Formel auf verwendeten Bereich beschraenken

Matrix-Formel auf verwendeten Bereich beschraenken
05.01.2008 11:49:00
Markus
Hallo Forum,
ich lasse mittels Makro in einem Exel-Sheet etliche Matrixfkt. eintragen, die Teilsummen der Tab berechnen. Bisher hatte ich einen Bereich definiert bis zur Zeile 6000, in die die Funktionen eingetragen werden und auch die Matrizen "laufen" nur bis R6000. Das gefaellt mir nun aber nicht mehr und so suche ich eine Moeglichkeit, wie ich z.B. folgender Matrix-Fkt. die tatsaechlich verwendeten Zeilen mitteile :
Range("Q3").FormulaArray = _
' "=IF(COUNTIF(R2C4:RC4,RC4)=1,SUM(IF(R2C4:R6000C4=RC4,IF(R2C13:R6000C13=""NP"",IF(R2C12:R6000C12"""",IF(R2C7:R6000C7=""€"",R2C12:R6000C12))))),"""")"
Koennt ihr mir da helfen? Statt R6000 moechte ich in den Formeln also die tatsaechliche (aber variable) Zeilenanzahl angeben.
Vielen Dank fuer euer Interesse und ggfls. Tipps, Gruesse,
Markus

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 12:31:00
Beverly
Hi Markus,

Range("Q3").FormulaArray = "=IF(COUNTIF(R2C4:RC4,RC4)=1,SUM(IF(R2C4:R" & loLetzte & "C4=RC4,IF( _
R2C13:R" & loLetzte & _
"C13=""NP"",IF(R2C12:R" & loLetzte & "C12"""",IF(R2C7:R" & loLetzte & "C7=""€"",R2C12:R" & _
loLetzte & "C12))))),"""")"


________________________________________

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 12:41:00
Markus
Hallo Karin,
so ganz schlau werde ich leider nicht aus deiner Lsg. Was ist denn unter loLetzte zu verstehen? Die Verknuepfung mit "& ..." ist mir klar. Hast du da eine Variable definiert? Muss gleich mal ausprobieren ob ich in der Art auch xlUp einsetzen kann?
Vielen Dank fuer deine Hilfe, ich melde mich gleich noma,
Markus

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 12:50:41
Beverly
Hi Markus,
sorry, hatte ich nicht mit kopiert

Dim loLetzte As Long
loLetzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row


Bis später,
Karin

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 13:07:53
Markus
Hallo Karin,
funzt leider nicht, ich bekomme bei einsetzen der Variablen in meine Formeln eine Fehlermeldung.
Auch als Variant definiert will er meine Variable nicht haben.
?
Idee was ich falsch mache?
LG,
Markus

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 13:14:11
Daniel
Hi
um das beurteilen zu können, müsstest du mal deine Datei mit dem Code hochladen.
es müssen ja nicht alle Daten drin sein.
Gruß, Daniel

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 17:00:00
Markus
Hallo Daniel,
tut mir leid, dass ich mich erst jetzt erst wieder melden kann. Der Code ist eigentlich ein ganz einfacher:

Sub MacroSomme()
' MacroSomme Macro
' Macro registrata il 15/05/2006 da MR
Application.ScreenUpdating = False
Range("Q3").FormulaArray = _
"=IF(COUNTIF(R2C4:RC4,RC4)=1,SUM(IF(R2C4:R6000C4=RC4,IF(R2C13:R6000C13=""NP"",IF(R2C12: _
R6000C12"""",IF(R2C7:R6000C7=""€"",R2C12:R6000C12))))),"""")"
Application.ScreenUpdating = True
End Sub


Da die Zeilenanzahl variabel ist, muss in der Matrixformel R6000 angepasst werden, was ich leider nicht hinbekomme.
Gruss,
Markus

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 17:09:59
Beverly
Hi Markus,
da waren doch nur beide von mir gepostete Teile zusammenzusetzen

Sub Markus()
Dim loLetzte As Long
Application.ScreenUpdating = False
loLetzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Range("Q3").FormulaArray = "=IF(COUNTIF(R2C4:RC4,RC4)=1,SUM(IF(R2C4:R" & loLetzte & "C4=RC4, _
IF( _
R2C13:R" & loLetzte & _
"C13=""NP"",IF(R2C12:R" & loLetzte & "C12"""",IF(R2C7:R" & loLetzte & "C7=""€"",R2C12: _
R" & _
loLetzte & "C12))))),"""")"
Application.ScreenUpdating = True
End Sub


Bis später,
Karin

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 18:38:00
Markus
Hallo Karin,
stimmt. Habe den Ursprungscode gepostet um keine voellige Verwirrung auszuloesen. Mein Code sah nach Einbau deiner Variablendef. bei mir ganz genau so aus, nur leider funzt das nicht. Schon bei Wechsel der Zeile in VBA kommt eine Fehlermeldung und die Makrozeile erscheint dann rot (Error of compilation).
Gruss,
Markus

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 19:06:00
Beverly
Hi Markus,
du musst die Trennstriche an der richtigen Stelle entfernen - das liegt an der Darstellung im Forum, nicht am Code selbst. Schau in die angehängte Beispielmappe, dort steht die Formel richtig drin.
https://www.herber.de/bbs/user/48826.xls
Bis später,
Karin

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 19:41:54
Markus
Ha!
Hallo!
Das wars, Karin! Klassse. Ich war so faul und dachte, ich muss die Leerzeichennicht eingebenvor und hinter dem &! Dachte Leerzeichen werden von VBA selbst eingefuegt? Oder verwechsle ich das mit der Grossschreibung bekannter Befehle? Lange nicht mehr an Makros rumgeschraubt, halt ...
Wie auch immer, nun funzt es auf diese Weise!
Prima, Danke und ein schoenes WE noch... :-)
Gruss,
Markus

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 20:05:35
Daniel
Hi
Leerzeichen werden manchmal von Excel eingefügt.
beim Zeichen & ist es nur leider so, daß dieses Zeichen auch für andere Funktionen verwendet wird (Variablendimensionierung)
daher muss, wenn es zum Verketten von Stirngs verwendet wird, mit dem Leerzeichen abgetrennt werden, um Verwechslungen auszuschließen.
Da Computer nun mal doof (aber schnell) sind, kann der VBA-Editor nicht erkennen, welcher Anwendungsfall gerade vorliegt und fügt deshalb nichts automatisch ein.
Das musst du dann selbst machen.
Gruß, Daniel

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 17:58:58
Daniel
Hi
kleine Alternative zu Beverlys vorschlag, das macht den Code der Formel vielleicht etwas übersichtlicher, weil die Formel nicht so zerpflückt ist:

Sub MacroSomme()
Dim strFormel As String
Dim strLetzte As String
strFormel = "=IF(COUNTIF(R2C4:RC4,RC4)=1,SUM(IF(R2C4:R6000C4=RC4,IF(R2C13:R6000C13=""NP"", IF( _
R2C12:R6000C12"""",IF(R2C7:R6000C7=""€"",R2C12:R6000C12))))),"""")"
strLetzte = "R" & CStr(Cells(65536, 4).End(xlUp).Row)
strFormel = Replace(strFormel, "R6000", strLetzte)
Range("Q3").FormulaArray = strFormel
End Sub


Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 19:22:00
Markus
Hi Daniel,
schoene Idee nur leider habe ich etwas geflunkert... der Code beeinhaltet eigentlich 4 Matrixformeln. Und fuer jede Matrixformel eine eigene Variable definieren...? Wird auch unuebersichtlich.
Ich poste jetzt mal das file, was war ich denn so geizig? Es gibt nix zu verheimlichen...:
https://www.herber.de/bbs/user/48828.xls
Gruss und Danke fuer eure Ausdauer,
Markus

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 19:45:00
Daniel
Hi
warum nicht 4 Variablen für 4 Martix-Formeln?
eingeben musst du das ganze Zahlen-und-Zeichengewirr ja sowieso irgend wann.
wenn du dann noch mehrfach ne Zeilenzahl jeweils durch ein " & lngLong & " ersetzen musst, dann wird noch unübersichlicher.
Durch die REPLACE-Funktion kannst du das viel einfacher lösen und du kannst deine bestehenden Formeln 1:1 weiterverwenden.
und je mehr Matrix-Formeln du hast, um so effektiver wird die Formel-Lösung, denn dann kannst du das Suchen-Ersetzen in einer Schleife abarbeiten:

Dim Formeln(4) as String
dim i as integer
dim strLetzeZeile as string
Formeln(1) = "Deine 1. Matrixformel"
Formeln(2) = "Deine 2. Matrixformel"
Formeln(3) = "Deine 3. Matrixformel"
Formeln(4) = "Deine 4. Matrixformel"
strLetzteZeile = "R1234"
For i = i to 4
Formeln(i) = Replace(Formeln(i), "R6000", strLetzteZeile)
next


spart also Massiv Codetext und ist damit auch wesentlich einfacher zu Pflegen
Gruß, Daniel

Anzeige
AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 19:54:00
Markus
Hallo Daniel,
Ok, ich gebe zu, dass das sehr elegant klingt aber irgendwie gefaellt mir die Vorstellung nicht, dass in meiner Array-Formel die Zahl 6000 erhalten bleibt und erst durch die Schleife die eigentliche Zeilenanzahl bestimmt und ersetzt wird. Fuer spaeteres Arbiten am oder NAchvollziehen des Makro erscheint mir das auch umstaendlicher.
Mit Copy und Paste gings rel. schnell die 6000 durch " & lngLong & " zu ersetzen.
Vielen Dank trotzdem!!
Beste Gruesse,
Markus

AW: Matrix-Formel auf verwendeten Bereich beschrae
05.01.2008 20:12:00
Daniel
Hi
jup, das schon
aber ich finde halt, daß die Formeln daduch doch sehr unübersichtlich und lang werden.
wenn man da nachträglich noch was verändern willst, viel spass.
wenn dich die 6000 als zu erseztender Wert stört, könnte man ja auch xxx oder sowas reinschreiben und dann diesen durch die Zeilenzahl ersezten, dann wäre es eindeutiger.
andererseits, auch der VBA-Editor hat eine SUCHEN-ERSETZEN-Funktion.
damit wärs noch schneller gegangen als mit Copy-Paste jeden Wert einzeln zu ersetzen.
Gruß, Daniel
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige