EXCEL VBA: Intelligentes copy/paste eines Bereichs mit Formeln und relativen Bezügen

Ich stelle mir das aufwendig vor.Gibt' s eine Prüfung, ob eine Zelle mindestens einen Bezug beinhaltet oder nur Zahlen, Kommas, Punkte, Vorzeichen, Klammern, ..?

a) Naja, geht so. Zum Zerlegen für Formeln habe ich was fertig.
<br />b) Nein, eine Formel ist ein Text, wie der zu Interpretieren ist bleibt einem selber überlassen.
<br />Andreas.
<br />Private Function IsRange(ByVal Expression As String) As Boolean
On Error Resume Next
IsRange = Not Application.Range(Expression) Is Nothing
End Function
<br />Function SplitFormula(ByVal Expression As String, Optional ByVal Locale As Boolean, Optional ByRef Positions) As Variant
'Split an Excel formula at delimiters
Const DefDelimiters = "=+-*/^%&<>()"
Dim Delimiters As String
'Dim Positions() As Long, Ret() As Variant
Dim Ret() As Variant
Dim Digit As String
Dim i As Long, Pos As Long, Count As Long
Dim InWord As Boolean
Dim InField As Integer

If Locale Then
Delimiters = DefDelimiters & _
Application.International(xlLeftBrace) & _
Application.International(xlRightBrace) & _
Application.International(xlListSeparator) & _
Application.International(xlColumnSeparator)
Else
Delimiters = DefDelimiters & "{},;"
End If

'Return the whole result for non-formulas
If Left$(Expression, 1) <> "=" Then
SplitFormula = Array(Expression)
Positions = Array(1)
Exit Function
End If

'Create space for max. possible positions
ReDim Positions(0 To Len(Expression)) As Long
Positions(0) = 1

For Pos = 2 To Len(Expression)
Digit = Mid$(Expression, Pos, 1)
i = InStr(Delimiters, Digit)
If i > 0 And InField = 0 Then
InWord = False
Count = Count + 1
Positions(Count) = Pos
If Count > 1 Then
'Don't break "<=" ">=" "<>"
Select Case Digit
Case "=", ">"
Select Case Mid$(Expression, Pos - 1, 1)
Case "<", ">"
Count = Count - 1
End Select
End Select
End If
Else
Select Case Digit
Case "'"
Count = Count + 1
Positions(Count) = Pos
Pos = InStr(Pos + 1, Expression, "'")
'Should not happen, just to be sure
If Pos = 0 Then Exit For
If Mid$(Expression, Pos + 1, 1) = "!" Then
InWord = True
Pos = Pos + 1
Else
InWord = False
End If
Case """"
InWord = False
Count = Count + 1
Positions(Count) = Pos
Pos = InStr(Pos + 1, Expression, """")
'Should not happen, just to be sure
If Pos = 0 Then Exit For
Case "["
InField = InField + 1
If Not InWord Then
InWord = True
Count = Count + 1
Positions(Count) = Pos
End If
Case "]"
InField = InField - 1
'Should not happen, just to be sure
If InField < 0 Then InField = 0
Case Else
If Not InWord Then
InWord = True
Count = Count + 1
Positions(Count) = Pos
End If
End Select
End If
Next

'Create space for the output
ReDim Preserve Positions(0 To Count)
ReDim Preserve Ret(0 To Count) As Variant
For Pos = 0 To Count - 1
Ret(Pos) = Trim$(Mid$(Expression, Positions(Pos), Positions(Pos + 1) - Positions(Pos)))
Next
'Grab last element
Ret(Pos) = Trim$(Mid$(Expression, Positions(Pos)))
'Assign results and return.
SplitFormula = Ret
End Function
<br />

Da bleibt nur eins: Die Lösung muss man sich programmieren.
<br />Zuerst macht man mal ein normales copy & paste, dadurch bleibt die Formel =7,50*1,19/12 erhalten.
<br />Ebenso ändert sich =B3*14+B5*12 zu =H37*14+H39*12 wenn man die Formeln irgendwo anders einfügt.
<br />Der problematische Bereich =A30/70 zu =52,41/70 ändern muss man selber programmieren.
<br />Eine Formel ist ein Text mit Trennzeichen, also können wir sie in Ihre Bestandteile zerlegen (der Formelinterpreter von Excel macht das ja auch).
<br />Die einzelnen Teile prüft man nun ob es sich um eine Zelle oder irgendwas anders anderes handelt.
<br />Wenn wir nun wissen das es sich um eine Zelle handelt und wir vorher den Bereich mit VBA kopiert haben, können wir via INTERSECT prüfen ob sich die Zelle in unserem Bereich befindet. Wenn dies nicht der Fall ist, dann wird es nun ein wenig knibbelig.
<br />Der Quellbereich und der Zielbereich ist nicht gleich, daher müssen wir diesen Versatz feststellen (einfach Ziel.Row - Quelle.Row, dito für Spalte).
<br />Für den gegebenen Fall wird aus A30 im Ziel dann G30 weil wir in Spalte H eingefügt haben. Nun referenzieren wir auf G30 in der Quelle und via OFFSET um dem ermittelten Bereich kommen wir auf A30 und holen daraus den Wert 52,41 und tauschen diesen mit G30 in unserer zerlegten Formel.
<br />Zum Schluss bauen wir die ganze Formeln wieder zusammen und schreiben diese in die Zelle.
<br />Für diese vereinachten Fälle geht das recht simpel, wenn wir jedoch berücksichtigen das es Array-Formeln und benannte Bereiche und Formeln um Namensmanager und Tabellen mit Formeln die [Überschrift] verwenden und ... extrem teuer oder man hat an den ganz heißen Tagen wo man eh nicht raus kann was zu tun.
<br />Andreas.
<br />

Vielen Dank für die schnelle und ausführliche Antwort.
<br />Ich hatte eine derartige Lösung befürchtet.
<br />Dann müsste man eine Art Parser für das "Ersetzen von Bezügen" in einer Formel in einer Zelle zu Werten schreiben: Hat das schon jemand probiert?
<br />Ich stelle mir das aufwendig vor.
<br />Gibt' s eine Prüfung, ob eine Zelle mindestens einen Bezug beinhaltet oder nur Zahlen, Kommas, Punkte, Vorzeichen, Klammern, ..?
<br />

In einer ExcelTabelle werden die Formeln mit Namen gebildet "Tabelle1[[#Diese Zeile];[Gehalt]]+Tabelle1[[#Diese Zeile];[Zulagen]]". Wandelt man die Tabelle in einen Bereich um, sehen die Formeln wie relative Bezüge in der Zeile aus "Mitarbeiter!$O2+Mitarbeiter!$P2".
Sortieren Sie die Datensätze neu, reagieren die Formeln aber nicht relativ. Es entsteht ein Chaos! Man muss die Formeln neubilden um richtige Bezüge herzustellen. Dieses Problem besteht auch in Excel 2010 Beta-Version.

Ja. Und?

Was ist denn die regelmäßige Aufgabe Deiner Tabelle und wie soll sie vor und nach der Bearbeitung aussehen.

Ein konkretes Beispiel wäre gut.

Viele Grüße,

Bernd

www.sulprobil.com

Hallo Claus,

1) ja, die Berechnung ist auf automatisch gestellt (auch ein klick auf neu berechnen/Arbeitsblatt berechnen hilft nicht).

2) Die Formeln sind mit copy -> paste (Verküpfung einfügen) eingegeben

3) die Nicht-Aktualisierung der Werte ist in allen Arbeitsblättern mit dieser Art des Bezugs (es gibt noch andere Bezüge in der Datei in verschiedenen anderen Arbeitsblättern, die bleiben aber seltsamerweise funktionell)

Wohlgemerkt: diese Bezüge funktionieren auch nicht, wenn ein völlig anderes Arbeitsblatt verschoben wird, das mit den Arbeitsblättern mit den oben beschriebenen Bezügen nicht in Verbindung steht!

Für mich ist das ein Rätsel (Ich arbeite schon seit über 30 Jahren anspruchsvoll mit Excel).

Noch eine Möglichkeit:

Die Datei wurde im Jahr 2003 mit Excel97 erstellt und immer wieder ergänzt/erweitert und letztes Jahr auf Excel 2013 konvertiert.

Ich habe aber schon mal alle "defekten" Bezüge in der Excel 2013 Version neu erstellt (siehe 2)