Herbers Excel-Forum - das Archiv

Dynamische Anpassung eines Zellbezugs ...

Bild

Betrifft: Dynamische Anpassung eines Zellbezugs ...
von: proxima05

Geschrieben am: 06.01.2007 17:02:50
Hallo,
ich habe eine Frage in Bezug auf einen relativen Zellbezug. Ich rechne in einer Formel in Spalte E mit einem Wert, der die Summe mehrerer Zeilen in Spalte D darstellt. Leider ist die Anzahl der Zeilen in der Spalte D nicht immer gleich, so dass ich auch den relativen Zellbezug in der Formel in Spalte E nicht fest vorgeben kann.
Wie kann man die Formel
=WENN(A14<>"";D14/$D$46;"")
in einen Code packen, damit anstelle $D$46 (dort steht aktuell die Summenformel) der aktuelle Zellbezug zur Summe benutzt wird. Es ist aber festgelegt, dass die Summenformel immer zwei Zellen unterhalb der letzten Zelle mit Inhalt in Spalte D geschrieben steht. Sicher gestellt ist das über den folgenden Code:
**********************************************************
Sheets("Tabelle1").range("D65536").End(xlUp).Select
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
**********************************************************
Danke für einen Tipp.
Gruß
Ralph
Bild

Betrifft: AW: Dynamische Anpassung eines Zellbezugs ...
von: Herby

Geschrieben am: 06.01.2007 21:58:09
Hallo Ralph,
mir ist nicht klar in welche Zeile die Formel zu schreiben ist ? (Zeile 14 oder ?)
=WENN(A14<>"";D14/$D$46;"")
Ich hab sie mal in die gleiche Zeile wie die Summe geschrieben. Must halt ggf.
noch anpassen. Die Summe wird auch nicht 2 Zeilen unterhalb der letzten Zahl
erstellt, sondern nur eine Zeile darunter. Passt das so ?
Die Werte A14 und D14 habe ich nicht dynamisch erstellt, sondern fix, wenn du sie
dynamisch willst, müsste ich wissen auf welche Zeile sie sich beziehen sollen.
Option Explicit
Sub rechne()
Dim lZ As Long
Dim Formel As String
Sheets("Tabelle1").Range("D65536").End(xlUp).Select
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
lZ = Sheets("Tabelle1").Range("D65536").End(xlUp).Row ' Zeile, in der die Summe steht
Formel = "=wenn(A14<>"""";D14/D" & lZ & ";"""")"
Range("E" & lZ).FormulaLocal = Formel
End Sub

Viele Grüße
Herby
Bild

Betrifft: AW: Dynamische Anpassung eines Zellbezugs ...
von: proxima05
Geschrieben am: 06.01.2007 22:05:09
Hallo Herby,
danke für Deine Antwort. Hat sich mit meiner Lösung überschnitten :-) ....
Gruß
Ralph
Bild

Betrifft: AW: Dynamische Anpassung eines Zellbezugs ...
von: proxima05
Geschrieben am: 06.01.2007 22:03:05
Proble, gelöst!
Anstatt mit Zellkoordinaten zu arbeiten, habe ich dem Summenfeld einen Namen gegeben und diesen in die Formel eingesetzt.
ActiveWorkbook.Names.Add Name:="Name_der_Zelle", RefersToR1C1:=Selection
Gruß
Bild

Betrifft: AW: Dynamische Anpassung eines Zellbezugs ...
von: fcs

Geschrieben am: 06.01.2007 22:12:06
Hallo Ralph,
stricke deinen Code für die Erstellung der Summenformel ein wenig um, dann kannst du über die berechnete Zeile für die Summenformel auch die Formel für die Zellen in Spalte E mit einem entsprechenden Bezug auf diese Zeile erstellen.
'Summenformel Spalte D erstellen
ZeileSumme = Sheets("Tabelle1").Range("D65536").End(xlUp).Row + 1
Sheets("Tabelle1").Cells(ZeileSumme, "D").FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
'Formel in Spalte E erstellen
Sheets("Tabelle1").Range("E14").FormulaR1C1 = "=IF(RC[-4]<>"""",RC[-1]/R" & ZeileSumme & "C4,"""")"

Dein Code fügt die Summenformel allerdings nicht 2 Zellen unterhalb des letzten Eintrags in Spalte D ein, sondern direkt darunter.
Gruss
Franz
Bild

Betrifft: AW: Dynamische Anpassung eines Zellbezugs ...
von: proxima05
Geschrieben am: 08.01.2007 07:51:19
Danke Franz, werde ich machen.
Gruß
Ralph
 Bild
Excel-Beispiele zum Thema "Dynamische Anpassung eines Zellbezugs ..."
Dynamischen Dialog erstellen Dynamische Postleitzahldatenbank
Dynamischer Monatsarbeitsplan Summe über dynamischen Bereich bilden
Dynamisches Diagramm Dynamische Generierung von Programmteilen
Dynamischer Blattname in SVERWEIS-Formel Dynamischer Hyperlink
Dynamischer Kalender mit Jahr, Quartal und Monaten Dynamische Gültigkeitsliste