Matrix-Formel kopieren und Spaltenbezug anpassen

Question

Hallo,

ich habe in einer Zelle eine Matrixformel

=SUMME(INDIREKT("$G$11:$G"&VERGLEICH("";$E:$E;-1)))/ANZAHL(INDIREKT("$F$1:$F"&VERGLEICH("";$E:$E;-1)))

die ich mit einem Abstand von 5 Spalten in die jeweils nachfolgenden Spalten mittels einem Code kopieren möchte. Allerdings klappt das nicht ohne weiteres, da die Berechnungsspalte "$G" ja fix ist aber fortgeschrieben werden muss. Wenn ich das "$" entferne, gibt es einen Fehler. Der Spaltenbezug $F kann dagegen erhalten bleiben. Hat jemand einen Tipp?

Danke und Gruß A.

Answer 1

Hallo Andreas,

anstatt die Formel mit VBA zu kopieren, könntest du die Formel auch gleich per VBA angepasst einfügen.

Wenn ich dich richtig verstehe steht die Formel, die du gepostet hast in Spalte H.

Versuche mal den folgenden Code:

Sub Formel()

Dim lngSpalte As Long
Dim strSpalte As String

For lngSpalte = 8 To 38 Step 5

 'Buchstabe für Spalte ermitteln
 strSpalte = Mid(Columns(lngSpalte - 1).Address, 2, InStr(1, Columns(lngSpalte - 1).Address, ":") - 2)
 'Formel einfügen
 Cells(3, lngSpalte).FormulaLocal = "=SUMME(INDIREKT(""" & strSpalte & "11:" & strSpalte & """ &VERGLEICH("""";E:E;-1)))/ANZAHL(INDIREKT(""F1:F""&VERGLEICH("""";E:E;-1)))"

Next lngSpalte

End Sub

Die Einfügezeile musst du natürlich noch anpassen, ebenso wie die letzte Einfügespalte.

Gruß

M.O.

Answer 2

Hi,

mal nur als Tipp: den Spaltenbuchstaben kann man einfacher ermitteln:

strSpalte = Application.Substitute(Cells(1, lngSpalte - 1).Address(False, False), 1, "")

Bis später, Karin

Answer 3

Hallo M.O. und Karin,

ihr habt mir wieder toll geholfen, funzt wunderbar, vielen Dank für eure guten Tipps!!!!

VG A.