Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Makro Formeln kopieren ohne Änderung Zellbezug auch für Array Formeln
#1
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
Antworten Top
#2
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.
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#3
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?
Antworten Top
#4
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 ...
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#5
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 …


Angehängte Dateien
.xlsx   ArrayFormelKopieren.xlsx (Größe: 8,89 KB / Downloads: 3)
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Flyto
Antworten Top
#6
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
Antworten Top
#7
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)
Antworten Top
#8
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
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste