Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1248to1252
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

Matrixformel in VBA umsetzen

Matrixformel in VBA umsetzen
Dirk
Hallo,
habe da ein Problem mit einer Matrixformel, die ich gerne in VBA abbilden möchte.
Die Formel errechnet mir den Mittelwert ohne 0.
{=MITTELWERT(WENN(C3:E3>0;C3:E3))}
über den Makrorekorder sieht es dann so aus
Selection.FormulaArray = "=AVERAGE(IF(RC[-3]:RC[-1]>0,RC[-3]:RC[-1]))"
Mein Vorhaben, ich möchte die Spalten und die Zeile variabel halten, ohne festen Bezug!
Kann mir bitte jemand dabei helfen?
Danke
Gruß Dirk
Der Bezug ist hier bereits variabel (bzw. relativ)
03.02.2012 14:54:28
NoNet
Hallo Dirk,
ich möchte die Spalten und die Zeile variabel halten, ohne festen Bezug!

Das ist schön - und Dein Makrorekorder kann anscheinend auch Deine Gedanken lesen - zumindest ist der Bezug in der aufgezeichneten Funktion bereits 'variabel' (bzw. : RELATIV).
Mit RC[-3] wird z.B. immer die Spalte referenziert, die sich 3 Spalten LINKS neben der Zielspalte befindet :
Führst Du den Code also z.B. aus während Zelle F5 aktiviert ist, dann bezieht sich die Funktion auf den Bereich C5:E5, wurde zuvor die Zelle H20 aktiviert, dann bezieht sich diese Funktion auf E20:G20 etc. Ist vor der Ausführung des Codes K10:L20 markiert, dann bezieht sich der Code für jede Zelle im markierten Bereich jeweils auf den Bereich 3 Spalten weiter links bis 1 Spalte weiter links (RC[-3]:RC[-1])
Schau dir auch mal folgenden (etwas älteren aber nach wie vor aktuellen) Beitrag von mir an :
http://www.spotlight-wissen.de/archiv/message/231967.html
Gruß, NoNet
Anzeige
die Werte in [-3] : [-1] als Variable eintragen
03.02.2012 15:16:39
Dirk
Hallo NoNet,
Danke für die ausführliche Beschreibung und den verlinkten Beitrag.
Ich möchte jedoch die Werte [-3] bzw.[-1] variabel angeben. Diese Werte sind nicht fix, sondern sollen ermittelt werden:
Beispiel? "=AVERAGE(IF(RC[-varBeginn]:RC[-varEnde]>0,RC[-varBeginn]:RC[-varEnde]))"
Geht so etwas?
Danke
Gruß Dirk
AW: die Werte in [-3] : [-1] als Variable eintragen
03.02.2012 15:33:57
NoNet
Hallo Dirk,
klaro geht das, denn diese Funktion wird hier ja in Form eines STRINGS angegeben, der sich "beliebig" zusammenbasteln lässt, z.B. so :
    Dim varBeginn As Variant, varEnde As Variant
varBeginn = -3
varEnde = -1
Selection.FormulaArray = "=AVERAGE(IF(RC[" & varBeginn & "]:RC[" & _
varEnde & "]>0,RC[" & varBeginn & "]:RC[" & varEnde & "]))"
Gruß, NoNet
Anzeige
Formel nach unten kopieren, aber wie?
03.02.2012 16:17:05
Dirk
Hallo NoNet,
vielen Dank, klappt wunderbar :-)
Wenn Du mir jetzt noch erklären könntest, wie ich die "Formel" aus Zeile 3 z.B. bis/nach Zeile 10 kopieren kann (per VBA natürlich) dann wäre mein Wochenende gerettet :-)
Danke!
Gruß Dirk
Lösung gefunden ... Danke NoNet :-)
03.02.2012 16:27:11
Dirk
Hallo NoNet,
der CODE sieht jetzt so aus und funktioniert auch wie gewollt!
Vielen Dank für Deine Unterstützung :-)
Ein schönes Wochenende wünscht
Dirk
Option Explicit
Sub MittelwertohneNull()
Dim varSpalte As Long
Dim varzähler As Variant
Dim zeile As Long, i As Long
varSpalte = Cells(2, Columns.Count).End(xlToLeft).Column
varzähler = -varSpalte + 1
i = 3
For zeile = i To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, varSpalte + 1).FormulaArray = _
"=AVERAGE(IF(RC[" & varzähler & "]:RC[-1]>0,RC[" & varzähler & "]:RC[-1]))"
i = i + 1
Next zeile
End Sub

Anzeige
Gerne - Das geht auch OHNE Schleife
03.02.2012 16:35:12
NoNet
Hallo Dirk,
schön, dass es nun passt.
Du kannst übrigens auch auf die FOR...NEXT-Schleife verzichten, indem Du die Funktion direkt in den entsprechenden Bereich einträgst :
    Range(Cells(3, varspalte + 1), _
Cells(Cells(Rows.Count, 1).End(xlUp).Row, varspalte + 1)).FormulaArray = _
"=AVERAGE(IF(RC[" & varzähler & "]:RC[-1]>0,RC[" & varzähler & "]:RC[-1]))"

Schönes WE, NoNet
ohne Schleife wird die Formel nicht angepasst
06.02.2012 08:27:27
Dirk
Guten Morgen NoNet,
die Variante ohne Schleife funktioniert bei mir leider nicht, jetzt steht das Ergebnis aus Zeile 3 in allen andere Zellen darunter auch! Warum?
Mach ich was falsch?
Wäre nett, wenn du mir das noch mal erklären könntest, Danke!
Gruß Dirk
Anzeige
ohne Schleife funktioniert es nicht, Frage offen
07.02.2012 08:18:26
Dirk
Guten Morgen,
könnte mir bitte jemand sagen, warum die Variante von NoNet (bei mir) nicht funktioniert, Danke.
Gruß Dirk

326 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige