Matrixformeln durch "normale Formeln" autauschen

Forumthread: Matrixformeln durch "normale Formeln" autauschen
7 Beiträge zu diesem Forumthread
Anzeige

Forumthread: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 11:56:40

anton100

Hallo Forum,

ich habe mit einer Datei das Problem, dass meine "normalen" Formeln (meist Summenprodukt) irgendwie durch Matrixformlen ersetzt wurden.
Nun möchte ich das gesamte Dokument nach den Matrixformeln durchsuchen und wieder die normalen Formeln eintragen

So ist es derzeit:
{=SUMMENPRODUKT((Terminplan!$C$3:$C$159=AG$7)*(Terminplan!$AK$3:$AK$159=$AG$2)*(JAHR(Terminplan!$BU$3:$BU$159)=$S8)*(ISOKALENDERWOCHE(Terminplan!$BU$3:$BU$159)=$T8)*(Terminplan!$BV$3:$BV$159))}

So soll es sein
=SUMMENPRODUKT((Terminplan!$C$3:$C$159=AG$7)*(Terminplan!$AK$3:$AK$159=$AG$2)*(JAHR(Terminplan!$BU$3:$BU$159)=$S8)*(ISOKALENDERWOCHE(Terminplan!$BU$3:$BU$159)=$T8)*(Terminplan!$BV$3:$BV$159))

Vielen Dank
Anton

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:07:26

RPP63
AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:32:00

Anton100
AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:42:00

RPP63
AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 13:06:40

Anton100
Ergänzung

03.12.2024 12:16:50

RPP63
AW: Ergänzung

03.12.2024 12:35:49

Anton100
AW: Ergänzung

03.12.2024 12:57:00

Anton100

AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:07:26

RPP63

Moin!
Teste mal an einer Kopie:

Sub no_array()

Dim x As Range, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

  For Each x In ws.Cells.SpecialCells(xlCellTypeFormulas)

    If x.HasArray Then x.Formula = x.FormulaArray

  Next

Next

End Sub

Gruß Ralf

AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:32:00

Anton100

Ich habe es lösen können. Hier der Code



Sub Matrixformeln_austauschen()

Dim lngZ As Long     'Zeilen

Dim intS As Integer  'Spalten

Dim Blatt As Worksheet



Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual



For Each Blatt In ActiveWorkbook.Worksheets



    Blatt.Select

    letztezeile = Blatt.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    letztespalte = Blatt.UsedRange.SpecialCells(xlCellTypeLastCell).Column

    

    For lngZ = 1 To letztezeile

        For intS = 1 To letztespalte

            

            Cells(lngZ, intS).Select

            If Cells(lngZ, intS).HasFormula = True Then

            Cells(lngZ, intS) = Cells(lngZ, intS).Formula

            End If

        Next intS

    Next lngZ



Next Blatt



Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True



End Sub

AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 12:42:00

RPP63

Zunächst mal: Gut, dass Du selbständig lösen wolltest!
Aber:
Schaue Dir mal meinen Code an.
Der dürfte erheblich schneller laufen, nutzt er doch SpecialCells(xlCellTypeFormulas) und braucht daher nicht über alle gefüllten Zellen iterieren.
Außerdem und wichtig:
Dein Code zerschießt alle Spill-Formeln!!
Ich prüfe implizit mittels .HasArray, ob ob es sich um eine "reine" Matrixformel handelt.

Nochmal für alle Mitleser:
Der Code von Anton100 kann sehr unangenehme Folgen haben.

Gruß Ralf

AW: Matrixformeln durch "normale Formeln" autauschen

03.12.2024 13:06:40

Anton100

Hallo RPP63,

vielen Dank für Deinen Hinweis. Ich habe Deinen Code ausprobiert und der ist wirklich sehr schnell. Leider findet der aber noch nicht alle Matrix Formeln. Irgendwo sind noch welche übrig geblieben. Hast Du noch eine Idee, wie ich mit Deinem Cide sicher alle Matrix-Formeln finde?

Danke

Ergänzung

03.12.2024 12:16:50

RPP63

Falls in einem Blatt keine Formeln stehen, läuft das Makro in einen Fehler.
Daher die Holzhammermethode bei erwartbaren Fehlern:

Sub no_array()

Dim x As Range, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

  On Error Resume Next

  For Each x In ws.Cells.SpecialCells(xlCellTypeFormulas)

    If x.HasArray Then x.Formula = x.FormulaArray

  Next

  On Error GoTo 0

Next

End Sub

AW: Ergänzung

03.12.2024 12:35:49

Anton100

RPP63 vielen Dank,

ich habe Deinen Tipp mit HasArray bei mir eingefügt und es läuft super.

If Cells(lngZ, intS).HasArray = True Then

Danke

AW: Ergänzung

03.12.2024 12:57:00

Anton100

Hallo RPP63,

irgendwie findert Dein Macro bei mir nicht alle Array Formeln. Hast Du noch einen Tipp, wie ich alle Formel in meinem Dokument mit vielen Blättern finde?

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