Clever-Excel-Forum

In der Aboversion gibt es seit längerem dynamische Formeln, die in einen Bereich "überlaufen".
Dazu gehören:

• SORTIEREN()
  
• SORTIERENNACH()
  
• FILTER()
  
• EINDEUTIG()
  
• SEQUENZ()
  
• ZUFALLSMATRIX()
  

Mal ein Beispiel in A1:
=ZUFALLSMATRIX(5;3;10;20;1)
liefert eine Matrix aus 5 Zeilen und 3 Spalten mit Ganzzahlen zwischen 10 und 20

	A	B	C	D
1	10	18	12
2	17	16	19
3	11	17	12
4	14	18	16
5	11	15	14
6

Zelle	Formel
A1	=ZUFALLSMATRIX(5;3;10;20;1)

Will man auf der Excel-Oberfläche diesen Bereich gegen die Werte austauschen, passiert folgendes:

• markiere ich eine beliebige Zelle (außer A1) kopiere und füge die Werte ein, passiert … NICHTS (außer einer Neuberechnung)!
  
• markiere ich die einzige Formelzelle A1, kopiere und füge den Wert ein, erhalte ich den Fehler #ÜBERLAUF!
  
• ich muss also erst den dynamischen Bereich markieren, dann kopieren und Werte einfügen, damit es funktioniert!
  

In VBA gibt es neue Methoden und Funktionen, damit man dies behandeln kann:

• wenn man die Formelzelle kennt, liefert Range("A1#") den dynamischen Bereich.
  
• ob sich eine Zelle im dynamischen Bereich befindet, überprüft man mit Range("B2").HasSpill (liefert true, wenn innerhalb von A1:C5)
  
• von einer beliebigen Zelle im dynamischen Bereich gelangt man mit Range("B2").SpillParent zur Formelzelle A1
  
• von der Formelzelle markiert man mittels Range("A1").SpillingToRange den überlaufenden Bereich
  

Wenn ich jetzt also mittels VBA die Formel gegen den Wert austauschen will, ginge folgendes:

• Die Formelzelle A1 ist bekannt:
  

• Sub Formula2Value1()
  Range("A1#").Value = Range("A1#").Value
  End Sub

• Ich weiß nicht, ob ich mich im Überlauf befinde:
  

• Sub Formula2Value2()
  If ActiveCell.HasSpill Then
    With ActiveCell.SpillParent.SpillingToRange
      .Value = .Value
    End With
  End If
  End Sub

Gruß Ralf