Absolute Bezüge herstellen

[Harald Friis]

Moin in die Runde,

eine Frage zu den absoluten und relativen Bezügen - für Excel 2010, aber
das spielt vermutlich keine Rolle:

Bekanntermaßen kann ich den Bezug mit Dollarzeichen absolut stellen. Nun
habe ich eine eher umfangreiche Tabelle, in der von einer Ausgangsformel
ca. 30 Spalten und 50 Zeilen diese Formel auto-ausgefüllt werden kann.

Die Formel in A1 ist also (beispielhaft) [Wert aus einer anderen Tabelle
in der dortigen A1]*n. In B1 wäre es dann das andere B1*n, in F5 das
andere F5*n. (Die Formel selbst bezieht sich auf eine
Summewenn-Berechnung, aber das ist für das Prinzip ja egal.)

Diese nun auto-ausgefüllte Tabelle müsste ich im nachhinein auf absolut
setzen (also mit Dollarzeichen versehen), da ein VBA-Code die Zeilen in
ein neues Blatt kopiert und ein Diagramm daraus baut.

Jetzt kenne ich derzeit zwei Möglichkeiten: ich nehme nicht
auto-ausfüllen, sondern schreibe händisch in jede Zelle die richtige,
absolut gesetzte Formel. Oder ich nehme das auto-ausfüllen und packe mit
F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit für lange
Zeit, bei der sich die Wahrscheinlichkeit eines Nervenzusammenbruchs
nahe 1 befindet.

Gibt es also eine Möglichkeit, automatisiert bestehende Formeln absolut
zu setzen? Mit VBA oder gar per sonstiger Methode?

Danke fürs Lesen bis zum Ende und Danke^n für Tipps und Hilfen.

Gruß

Harald Friis

[Claus Busch]

Hallo Harald,

Post by Harald Friis
Diese nun auto-ausgefüllte Tabelle müsste ich im nachhinein auf absolut
setzen (also mit Dollarzeichen versehen), da ein VBA-Code die Zeilen in
ein neues Blatt kopiert und ein Diagramm daraus baut.

warum kopierst du hier nicht die Werte? Und warum soll ein Diagramm
nicht aus relativen Formeln erstellt werden können?

Post by Harald Friis
Gibt es also eine Möglichkeit, automatisiert bestehende Formeln absolut
zu setzen? Mit VBA oder gar per sonstiger Methode?

Da du auf andere Tabellen zugreifst, hast du das Ausrufezeichen in der
Formel. Du könntest mit Suchen und Ersetzen den ersten Teil erledigen
Suchen nach !A und Ersetzen durch !$A$.
Das Gleiche dann noch mit dem Doppelpunkt.
Suche nach :A und ersetzen mit :$A$

Oder du schreibst dir in VBA ein Array mit allen in den Formeln
vorkommenden Buchstaben und durchläufst dann alle Zellen des Blattes mit
Formeln und dieses Array und ersetzt die in der Formel gefunden
Buchstaben durch in Dollarzeichen eingepackte Buchstaben.
Gewissermaßen also finde A und ersetze durch $A$ und das für alle
Buchstaben.


Mit freundlichen Grüßen
Claus

[Harald Friis]

Hallo Claus,

Post by Claus Busch
warum kopierst du hier nicht die Werte? Und warum soll ein Diagramm
nicht aus relativen Formeln erstellt werden können?

das Kopieren der Werte habe ich auch überlegt. Allerdings müsste es bei
Änderungen immer neu erstellt werden. Wäre eine Notlösung gewesen. Das
Diagramm kann man auch aus relativen Formeln erstellen. Bloß greift das
neue Blatt nicht mehr auf die echten Zahlen. Wenn in Zeile 10 auf die
Zeile 10 der ersten Tabelle zugegriffen wird, diese Zeile aber in die
Zeile 1 kopiert wird, dann klappt es halt nicht so, wie es soll. Das
lässt sich - entsprechende, bei mir nicht ausreichend vorhandene
VBA-Kenntnisse vorausgesetzt - sicher auch eleganter lösen. ;-)

Post by Claus Busch
Da du auf andere Tabellen zugreifst, hast du das Ausrufezeichen in der
Formel. Du könntest mit Suchen und Ersetzen den ersten Teil erledigen
Suchen nach !A und Ersetzen durch !$A$.
Das Gleiche dann noch mit dem Doppelpunkt.
Suche nach :A und ersetzen mit :$A$

Gutes kann so einfach sein. Da wäre ich nicht drauf gekommen. Es klappt
wunderbar. Herzlichen Dank.

Gruß

Harald Friis

[Claus Busch]

Hallo Harald,

Post by Harald Friis
Gutes kann so einfach sein. Da wäre ich nicht drauf gekommen. Es klappt
wunderbar. Herzlichen Dank.

mit VBA dann:

Sub Formel()
Dim rngC As Range
Dim myStr As String
Dim myArr As Variant
Dim i As Long

myStr = "A,B,C,D,E,F,G,H,I,J,K"
myArr = Split(myStr, ",")

With ActiveSheet
For Each rngC In .UsedRange
If rngC.HasFormula Then
For i = LBound(myArr) To UBound(myArr)
rngC.FormulaLocal =
Replace(Replace(Replace(Replace(rngC.FormulaLocal, _
"!" & myArr(i), "!$" & myArr(i) & "$"), ":" &
myArr(i), _
":$" & myArr(i) & "$"), ";" & myArr(i), ";$" &
myArr(i) & "$"), _
"(" & myArr(i), "($" & myArr(i) & "$")
Next
End If
Next
End With
End Sub

Achte auf die Zeilenumbrüche!


Mit freundlichen Grüßen
Claus

[Harald Friis]

Hallo Claus,
auch dafür danke, ich teste es aus.

Gruß

Harald Friis

[HC Ahlmann]

Post by Harald Friis
Jetzt kenne ich derzeit zwei Möglichkeiten: ich nehme nicht
auto-ausfüllen, sondern schreibe händisch in jede Zelle die richtige,
absolut gesetzte Formel. Oder ich nehme das auto-ausfüllen und packe mit
F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit für lange
Zeit, bei der sich die Wahrscheinlichkeit eines Nervenzusammenbruchs
nahe 1 befindet.

3. Möglichkeit: Die Formel enthält doch für jede absolut zu beziehende
Zelle eine Bezeichnung und ein paar vorangehende Zeichen, die einen
unverwechselbaren String ergeben, den man mit Suchen und Ersetzen um das
Dollar-Zeichen ergänzen kann.