Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1448to1452
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
Inhaltsverzeichnis

VBA Rechenzeit fuer Matrixformel verkuerzen

VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 17:20:24
MarkusX
Hallo Forum,
hat jemand eine Idee, wie ich fuer folgenden Code die Rechenzeit noch weiter optimieren koennte:
Sub ZeigeWerte()
' Matrixformel
Dim ur As Long ' ur = letzte Zeile der Spalte A
Dim record As Integer  'bis zu 1000 gefundene Zeilen des Kriteriums
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ur = Sheets("Tabelle_1").Range("A65536").End(xlUp).Row
Range("J7").FormulaR1C1 = "=COUNTIF('Tabelle_1'!R2C1:R" & ur & "C[-2],R4C10)"
record = Sheets("Scelta cliente").Range("J7").Value
For zeile = 1 To record
Range("A3").Offset(zeile, 0).FormulaArray = _
"=INDEX('Tabelle_1'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW(' _
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Next zeile
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Vorab vielen Dank fuer euer Interesse und eure Tips.
Gruesse
Markus X

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 17:35:21
Daniel
Hi
- soweit wie möglich, Zellen nicht einzeln per Schleife, sondern zusammen befüllen.
da man einzeilige Matrixformeln nicht in mehrere Zellen gleichzeitig schreiben kann, weil Excel dann mehrzeilige Matrixformeln daraus macht, befüllt man hier die erste Zelle einzeln und kopiert dann die Formel in die restlichen Zellen.
- Wenn die Anzahl der Werte nur für das Makro benötigt wird, kann man das CountIf auch direkt in VBA anwenden, ohne die Formel in eine Zelle schreiben zu müssen.
...
Application.Calculation = xlCalculationManual
ur = Sheets("Tabelle_1").Range("A65536").End(xlUp).Row
record = Worksheetfunction.CountIf(Sheets("Tabelle1").Range("A2:H" & ur), Range("J10"))
Range("A3").Offset(zeile, 0).FormulaArray = _
"=INDEX('Tabelle_1'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('  _
_
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Range("A3").Copy
Range("A4".Resize(record - 1).pastespecial xlpasteall
Application.Calculation = xlCalculationAutomatic
...
gruß Daniel

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 18:54:34
MarkusX
Hallo Daniel,
danke fuer Deine Idee, die ich versuche anzuwenden, denn so richtig funktioniert das glaube ich hier nicht - aber vllt. verstehe ich es auch nicht richtig...
CountIf wird auch in der Tabelle benoetigt. record ist variabel und nummerisch und bestimmt die Anzahl Datensaetze zu bestimmten Codes (Zeichenfolgen). Die Matrixformel "holt" dann den gesuchten Wert in der entsprechenden Fundzeile.
Ich hatte den Code nun wie folgt abgeandert:
...
Range("J7").FormulaR1C1 = "=COUNTIF('Tabelle_1'!R2C1:R" & ur & "C[-2],R4C10)"
record = Sheets("Tabelle_2").Range("J7").Value
For zeile = 1 To record
Range("A4").Offset(zeile, 0).FormulaArray = _
"=INDEX('Tabelle_1'!C[1],LARGE(IF('Tabelle_1'!R2C1:R" & ur & "C1=R4C10,ROW('Tabelle_1'!R2:R" & ur & "))," & zeile & "))"
Range("A4").Copy
Range("A5").Resize(record - 1).PasteSpecial xlPasteAll
Next zeile
...
Hattest Du das so gemeint? Leider holt die Matrix aber nicht die gesuchten Werte.
Was mach ich da falsch, oder koennen wir das so nicht machen?
Danke und Gruss
Markus X

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 20:00:41
Daniel
Hi
also meine Grundidee war, dass du die ArrayFormel nicht per Schleife in jede Zelle einträgst, sondern:
- die Arrayformel in die erste Zelle einträgst
- diese Zelle kopierst und in die restlichen Zellen einfügst.
ohne deine Datei zur verfügung zu haben, kann ich aber das alles nicht testen und dir auch keinen konkreten und funktionieren den Code sagen, sondern nur das Prinzip.
Gruss Daniel

AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 21:01:20
MarkusX
Hallo Daniel,
die Grundidee ist absolut einleuchtend, ich versuche mich noch daran und danke Dir fuer Deine Unterstuetzung!! Hier kannst Du Dir mal eine bereinigte Datei anschauen:
https://www.herber.de/bbs/user/100456.xlsm
Die Datensaetze werden ueber den Button/Makro "Nuova richiesta" (Modul 3) geloescht, so dass dann eine neue Auswahl mgl. wird und werden dann ueber Button/Makro "Mostra record" (Modul 2) angezeigt. Ist natuerlich jetzt ziemlich leer die Datei, um auf die erf. Dateigroesse zu kommen... Hoffe man kann's noch nachvollziehen...
Viele Gruesse
Markus X

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 21:27:53
Daniel
Hi
noch ne Optimierungmöglichkeit, gerade wenns viele Datensätze sind:
lagere den Formelteil, welcher die Zeilennummer berechnet in eine Hilfsspale aus:
KGRÖSSTE(WENN('Dettagli record'!$A$2:$A$9=$J$4;ZEILE('Dettagli record'!$2:$9))
und verwende in den Datenspalten nur die Indexfunktion, welche sich dann die Zeilennummer aus dieser Hilfsspalte holt.
Da sich die Zellwerte einer Zeile in der Auswertung immer auf die selbe Zeile in der Datentabelle beziehen, muss man ja die Berechung der Zeilennummer nicht in jeder Zelle machen, sondern nur einmal pro Zeile.
Und da diese Berechnung der Zeilennummer der Hauptrechenaufwand ist, spart das viel Rechenzeit.
Gruss Daniel

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 22:02:22
MarkusX
Hey Daniel,
auch noch am Start .... :). Guck mal, ich habe nun Deine Grundidee mal ein wenig umgebastelt und komme nun der Lsg. schon sehr nahe :
....
For zeile = 1 To record
' ID Ordine
Range("A4").Offset(zeile - 1, 0).FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Range("A" & zeile + 3).Copy
If record = zeile Then Exit Sub Else
Range("A" & zeile + 4).Resize(record - zeile).PasteSpecial xlPasteAll
Next zeile
....
Problem: die Zeile "If record = zeile Then Exit Sub" bricht die Schleife zwar an der richtigen Stelle ab, aber ohne CutCopy.
Es ist schon spaet, ich steh auf dem Schlauch, kannst Du vllt. weiterhelfen?
Deine andere Idee muss ich mir morgen angucken... moechte erst die Schleife hinbekommen ...
Gruss
Markus X

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
28.09.2015 22:16:33
Daniel
hi
hatte ich dir nicht gesagt, dass das kopieren der Formel und einfügen in den restlichen Zellbereich die Schleife ersetzen soll?
Die For-Next-Schleife kann dann vollständig entfallen.
also einfach so:
Range("A4").FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW(' _
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Range("A4").Copy
Range("A4").Resize(record).PasteSpecial xlpasteall
mehr nicht.
keine Schleife, kein For-Next, kein IF-Then oder sonstiges
ich wiederhols nochmal für dich:
KEIN FOR - NEXT !!!!!!!!
die Formel wir nur in die erste Zelle eingetragen und dann per Copy-Paste in die restlichen Zellen auf einen Schlag gleichzeitig geschieben, ohne For-Next-Schleife!!!
Gruss Daniel

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 09:16:12
MarkusX
Hallo Daniel,
hallo Forum
ich gebe zu, ich gerate hier an die Grenzen meines VBA-Verstaendnisses. Aber "also einfach so" funktioniert Dein Vorschlag leider nicht, da in der Matrix-Formel die Variable "zeile" benoetigt wird, um die Ergebnisse absteigend anzuzeigen. Prinzipiell kapier ich ja was du meinst, aber wie bekomme ich denn diese Variable ohne FOR-NEXT rein?
Vielleicht hat jemand anders weitere Ideen, oder kann mir auf die Spruenge helfen? Ich bemuehe mich ja und bin somit fuer jede Hilfe dankbar.
Gruesse
Markus X

AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 10:58:55
Daniel
Hi
ganz einfach mit der Funktion ROW() (und die verwendest du ja schon in der Formel)
in deinem Fall dann statt:
..."))," & zeile & "))"

dieses:
...")),Row(R[-3]C1))"
der Versatzwert -3 Berechnet nich aus der Zeile, in welche du die Formel als erstes einträgst und dem Startwert, den diese Funktion in der ersten Zeile ausgeben soll (in deinem Fall erste Zeile: 4 und Startwert 1 ergibt als versatz: 1-4 = -3)
Gruß Daniel

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 12:01:19
MarkusX
Hallo Daniel,
ja mit Zeile() koennte es gehen, allerdings scheinbar nicht so wie Du meinst:
...,Row(R[-3]C1))"
das liefert direkt in dieser Stelle des Codes die Fehlermeldung: FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden ...
aus dem Grunde hab ich Zeile() wie folgt genutzt:
...,Row()-3))"
das liefert allerdings in der Resize-Stelle einen Fehler: Teile eines Arrays koennen nicht geandert werden.
Verstehst Du was da los ist?
Gruss
Markus X

AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 13:03:18
Daniel
Hi
dann hast du dir irgendwann irgendwie ein Mehrzeiliges Array erstellt, welches du erst löschen musst.
mehrzellige Arrayformeln kann man aber nur bearbeiten, wenn man alle Zellen die dazugehören gleichzeitig bearbeitet.
klicke hierzu erst eine dieser Zellen an und wähle dann im Menü
Start - Bearbeiten - Suchen und Auswählen - Inhalte auswählen - Aktuelles Array
um alle Zellen zu markieren die zu diesem Array gehören um alle zusammen löschen zu können.
Gruß Daniel

Anzeige
AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 13:41:35
MarkusX
Hallo Daniel,
Mehrzelliges Array? Meine Matrixformel greift auf verschiedene Zellen in verschiedenen Arbeitsblaettern zu, deshalb verwende ich sie ja. Meinst Du das? "Inhalte auswaehlen" liefert immer nur die eine Zelle A4 in die das Makro gerade die Array Formel geschrieben hat.
Meinst Du es koennte mit Windows 10 zu tun haben? So einen Fall hatte ich dieser Tage naemlich schonmal...
Gruss
Markus X

AW: VBA Rechenzeit fuer Matrixformel verkuerzen
29.09.2015 14:16:26
Daniel
Hi
ok ich hatte da im zweiten anlauf einen Fehler drin, so sollte es gehen:
1. trage die Array-Formel in die erste Zelle ein (A4)
2. kopiere diese Zelle
3. füge dann diese kopierte Zelle ab Zelle A5 in die restlichen Zellen ein. die Zelle A4, welche die Arrayformel bereits enthält, darfst du nicht mehr überrschreiben.
dh im Prinzp so
Range("A4").FormulaArray = "=Deine Arrayformel"
Range("A4").Copy
Range("A5").Resize(Anzahl - 1).PasteSpecial xlPasteall
anzahl ist die Anzahl der Zellen, in welche die Matrixformel übernommen werden muss, Anzahl - 1, weil in der ersten Zelle die Formel ja schon steht.
Gruß Daniel

Anzeige
Geloest
29.09.2015 15:03:41
MarkusX
Hallo Daniel,
Dein Anzahl habe ich durch mein record ersetzt, und wie gesagt Rows()-3 entsprechend korrigiert. Fuer die Nachwelt, so funktioniert der Code somit:
...
record = Sheets("Scelta cliente").Range("J7").Value ' in der hochgeladenen Beispielmappe = 8
Range("A4").FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('Dettagli record'!R2:R" & ur & ")),Row()-3))"
Range("A4").Copy
Range("A5").Resize(record - 1).PasteSpecial xlPasteAll
...
Voellig geil! Rechenzeit mind. halbiert!
Danke nochmal fuer die Unterstuetzung und Geduld ... !!
Gruss
Markus X

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige