Microsoft Excel

Herbers Excel/VBA-Archiv

Matrixformel und Autofill per VBA


Betrifft: Matrixformel und Autofill per VBA von: Uwe
Geschrieben am: 07.08.2018 09:49:08

Hallo zusammen,
meine Herausforderung ist es in der Tabelle1 (Zelle G23) folgende Matrixformel mittels VBA einzutragen:

.Cells(23, 8).FormulaArray = IF(A23="","",SUM(IF((Tabelle3!$A$1:$A$10000=Tabelle1!A23)*(Tabelle3!$K$1:$K$10000=Tabelle1!$G$22),1)))

Dies funktioniert auch soweit.

Mein Thema ist nun, das sich nach schreiben dieser Formel in die Zelle die geschweiften Klammern (Matrixformel STRG-SHIFT_ENTER) nicht per VBA aktivieren lassen.
Zudem soll diese Formel via Autofill-Methode ab G23 bis zur letzten gefüllten Zeile mit dem jeweiligen Zeilenbezug ('A23, A24,...) einkopieren.
Habe mit R1C1, Evaluate usw. herumprobiert und bekomme es einfach nicht hin.
Wäre Euch sehr dankbar für einen Tip.

Folgend mein Skript (Modul):

Public Sub Summary_Total_Formula()

'Formeln im Angebot runterkopieren
Dim lngLetzte As Long      'letzte Zeile

With ThisWorkbook.Sheets("Tabelle1")
.Cells(23, 7).FormulaArray = "=IF(RC[-6]="""","""",SUM(IF((Tabelle3!R1C1:R10000C1=Tabelle1!RC[-  _
_
6])*(Tabelle3!R1C11:R10000C11=Tabelle1!R22C7),1)))" 'LDM
End With

With ActiveSheet    'Dev 1 Formel runterkopieren
   'letzte Zeile auffinden:
   lngLetzte = .UsedRange.Rows.Count + .UsedRange.Row - 1
   'Formel aus G23 runterkopieren:
   .Range(.Cells(23, 7), Cells(lngLetzte, 7)).Formula = .Cells(23, 7).Formula
End With

End Sub

  

Betrifft: AW: Matrixformel und Autofill per VBA von: Daniel
Geschrieben am: 07.08.2018 10:27:32

HI

hast du schon mal probiert, die Formel mit Copy-Paste zu übertragen?

.Cells(23, 7).Copy Destination:= .Range(.Cells(24, 7), .Cells(lngLetzte, 7))
Ansonsten sieht die Formel nach einem einfachen ZählenWenns aus, welches du als normale Formel direkt in die Zellen schreiben könntest, ohne Arrayabschluss.
.FormulaR1C1 = "=IF(RC1="""","""",CountIfs(Tabelle3!C1,RC1,Tabelle3!C11,R22C7))"
im für ältere Excelversionen auch Summenprodukt, aber bitte ohne das WENN
In Berechnungen wird WAHR wie 1 und FALSCH wie 0 gewertet.
Ohne das WENN sollte das Summenprodukt auch ohne Matrixformelabschluss funktionieren.

noch ein Tip: bei einem Zellbezug auf eine Zelle im gleichen Tabellenblatt sollte man in der Formel die Tabellenblattangabe weglassen. Ansonsten kann es zu unerwünschten Effekten kommen. (und die Formel wird kürzer.

Gruß Daniel


  

Betrifft: AW: Matrixformel und Autofill per VBA von: Robert Werner
Geschrieben am: 07.08.2018 10:29:50

Hallo Uwe,

bei mir wird mit Deinem Code in G23 die gewünschte Arrayformel eingetragen. Diese Arrayformel wird nur durch Dein "AutoFill" wieder überschrieben. Dort befüllst Du den Bereich ja mit Formula und nicht mit FormulaArray.

Dein AutoFill erreichst Du wie folgt:

With ActiveSheet    'Dev 1 Formel runterkopieren
   'letzte Zeile auffinden:
   lngLetzte = .UsedRange.Rows.Count + .UsedRange.Row - 1
   'Formel aus G23 runterkopieren:
   .Cells(23, 7).AutoFill Destination:=.Range(.Cells(23, 7), Cells(lngLetzte, 7)), Type:= _
xlFillDefault
End With
Gruß
Robert


Beiträge aus dem Excel-Forum zum Thema "Matrixformel und Autofill per VBA"