Makro Formeln kopieren ohne Änderung Zellbezug auch für Array Formeln

[Flyto]

Liebe clever-Excel Forum Mitglieder!

Hab mal wieder eine Frage - seit dem letzten Mal hat sich einiges getan und und bin schon etwas flotter mit VBA. Habe nun ein Makro mit dem ich Formeln kopieren kann, ohne dass sich die Zellbezüge ändern.
Allerdings funktioniert das nicht mit Arrayformeln. Was müsste ich da an dem Makro ändern/inwiefern müsste das angepasst werden damit ich auch Arrayformeln kopieren kann?

Das ist der Makrocode

Code:

Sub FormelnKopieren()

Dim rngQuellbereich As Range
Dim rngZielbereich As Range

 Set rngQuellbereich = Selection

 If Selection.Count = 1 Then
   MsgBox "Bitte markieren Sie die Zellen, aus " & _
   "denen Formeln kopiert werden sollen!", _
   vbCritical + vbOKOnly, "
   Exit Sub
 End If

 If Selection.Areas.Count > 1 Then
   MsgBox "Eine Mehrfachauswahl kann nicht " & _
   "kopiert werden!", vbCritical + vbOKOnly, _
   
   Exit Sub
 End If

 If IsNull(rngQuellbereich.HasArray) Then
   MsgBox "Arrayformeln koennen nicht kopiert werden!", _
   vbCritical + vbOKOnly,
   Exit Sub
 End If

 Set rngZielbereich = Application.InputBox( _
 "Bitte markieren Sie den Bereich, " & _
 "in den Sie die Formeln kopieren moechten:", Type:=8)

If rngZielbereich.Parent.ProtectContents Then
   MsgBox "Der Zielbereich ist geschuetzt.", _
   vbCritical + vbOKOnly,
   Exit Sub
 End If

 If rngQuellbereich.Count <> rngZielbereich.Count Or _
   rngQuellbereich.Rows.Count <> _
   rngZielbereich.Rows.Count Or _
   rngQuellbereich.Columns.Count <> _
   rngZielbereich.Columns.Count Then

   MsgBox "Der Zielbereich muss genauso gross sein, " & _
   "wie der markierte Quellbereich!", _
   vbCritical + vbOKOnly,
   Exit Sub
 End If

 rngZielbereich.FormulaLocal = _
 rngQuellbereich.FormulaLocal

End Sub

Da gibt es ja auch

Code:

If IsNull(rngQuellbereich.HasArray) Then
   MsgBox "Arrayformeln koennen nicht kopiert werden!", _
   vbCritical + vbOKOnly,
   Exit Sub
End If

Danke schonmal für eure Hilfe und lg!


Crosspost: http://www.herber.de/forum/archiv/1640to...tm#1642135

[schauan]

Hallöchen,

also, es kommt mehr oder weniger darauf an, wie die Array- / Matrixformeln eingegeben wurden. Ich kann eine Formel in eine einzelne Zelle eingeben oder auch mehrere Zellen markieren und die Formel eingeben, ohne das das eigentlich nötig ist. Das verursacht das Problem.

Wenn es jedoch nötig war, eine Matrixformel über mehrere Zellen einzugeben macht es sicher keinen Sinn, diese zu trennen. Dann sollte man die komplette Matrix kopieren.

[Flyto]

Hallo schauan,

danke für deine Antwort! Ich verstehe leider nicht ganz was du mit "über mehrere Zeilen" meinst. Habe in jeder Zelle eine Formel drin stehen. Wenn diese Formeln normal mit Enter abgeschlossen werden kann ich mehrere Zellen markieren und mit diesem Makro kopieren ohne dass sich die Bezüge ändern. Wenn ich die Zelle jedoch mit Strg-Shift-Enter abschließe (also eine Matrixformel erzeuge) funktioniert das Makro nicht mehr. Das würde ich gerne ändern - ist das denn möglich?

[schauan]

Hallöchen,

dann mal anders herum und ohne Hintergund - kannst Du die gleiche Aktion manuell durchführen? Also wirklich die gleichen Zellen nehmen wie im Makro und kopieren usw? Wenn Du manuell die Fehlermeldung erhältst, geht es per VBA auch nicht ...

[schauan]

Hallöchen,

ich habe hier im Anhang mal noch ein Beispiel. Schaue Dir die Formeln in Spalte B und C an. Sehen doch gleich aus, oder?
Nun versuche, B1 nach B2 zu kopieren.
Mache den gleichen Versuch mit C1 und C2.


In Spalte B habe ich bei der Formeleingabe B1 bis B3 markiert. Dadurch wurde mir gleich in alle 3 Zellen die Formel eingetragen.
In Spalte C habe ich die Formel in C1 eingetragen und dann nach C2 und C3 kopiert …

[Flyto]

Hallo schauan,

vielen dank für deine Antwort und entschuldige meine späte Rückmeldung!

Ach ich verstehe, das hatte ich die Tage mal dass die verknüpft waren, da hab ich mich auch gewundert:D
Aber nein, daran kann es nicht liegen - die Formeln lassen sich manuell kopieren. Ich denke jedoch, dass im Makro ein Teil fehlt oder das kpieren von Matrix formeln blockiert wird (eben um derartige Fehlermeldungen zu vermeiden falls die Formeln verknüpft sind.

Danke und lg

[Flyto]

Code:

Sub FormelnKopierenArray()

Dim rngQuellbereich As Range
Dim rngZielbereich As Range

 Set rngQuellbereich = Selection

 If Selection.Count = 1 Then
   MsgBox "Bitte markieren Sie die Zellen, aus " & _
   "denen Formeln kopiert werden sollen!", _
   vbCritical + vbOKOnly, ""
   Exit Sub
 End If

 If Selection.Areas.Count > 1 Then
   MsgBox "Eine Mehrfachauswahl kann nicht " & _
   "kopiert werden!", vbCritical + vbOKOnly, _
   ""
   Exit Sub
 End If


 Set rngZielbereich = Application.InputBox( _
 "Bitte markieren Sie den Bereich, " & _
 "in den Sie die Formeln kopieren moechten:", Type:=8)

If rngZielbereich.Parent.ProtectContents Then
   MsgBox "Der Zielbereich ist geschuetzt.", _
   vbCritical + vbOKOnly, ""
   Exit Sub
 End If

 If rngQuellbereich.Count <> rngZielbereich.Count Or _
   rngQuellbereich.Rows.Count <> _
   rngZielbereich.Rows.Count Or _
   rngQuellbereich.Columns.Count <> _
   rngZielbereich.Columns.Count Then

   MsgBox "Der Zielbereich muss genauso gross sein, " & _
   "wie der markierte Quellbereich!", _
   vbCritical + vbOKOnly, ""
   Exit Sub
 End If

 rngZielbereich.FormulaLocal = rngQuellbereich.FormulaLocal

 rngZielbereich.FormulaArray = rngZielbereich.Formula
 
End Sub

Soweit habe ich es jetzt, allerdings kopiert er komischerweise die Formel nicht 1:1 sodass sich die Bezüge verändern (und zwar verschieben sich nicht fixierte Zeilen jeweils +1 nach unten)

[Gast 123]

Hallo

dann achte bitte auf -relative und absolute- Bereichsangaben!!  Erkennbar am  '$'  Zeichen!!
Ich nehme an vor die Zeilenangabe der Formel must du das $ Zeichen setzen. Probier es bitte aus!!
Beispiel:   =Summe("A1:A10")  oder  =Summe("A$1:A$10")   ($ gilt auch für Spalten)

mfg  Gast 123