summenformel mit relativen Bezügen (VBA)

[Furesta]

Hallo,
ich weiß, für Euch sicher eine triviale Frage ...
... aber ich bin Anfänger.
Ich möchte eine Summenformel in Spalte 6 mit relativen Bezügen eintragen und habe programmiert:
 
    Dim x
    ActiveSheet.UsedRange.Select
    x = Selection.Rows.Count
    Cells(x , 6).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-1]:R[-x]C[-1])"
... "x" wird richtig berechnet, ich bekomme aber die Fehlermeldung
Laufzeitfehler '1004'
Anwendungs- oder objektorientierter Fehler

Sicher nur eine Kleinigkeit für Profis, aber ich finden den Fehler in der Formel nicht.
Bitte Hilfe
Gruß
Rolf

[ralf_b]

die formel ist ein text also kann die variable x  dort nicht funktionieren.   Text wird mit "&"  verbunden  "text" & variable & "text"

[Gast 123]

Hallo

ixh gkaube das ist noch ein Denkfhler in der Formel!  Was ist mit den Variablen R + C ??  Die beiden Werte R+C sind nicht gesetzt!  = Null
Ausserdem sollte man auf Selectt verzichten.  Aber die Formel muss richtig angepasst werden! Sonst geht sie Rückwaerts über das Blatt hinaus!

Code:

    x = ActiveSheet.UsedRange.Rows.Count
    Cells(x, 6).FormulaR1C1 = "=SUM(R[-1]C[-1]:R[-x]C[-1])"

mfg Gast 123

[Kuwer]

Hallo Rolf,

das wäre mein Vorschlag:

Code:

Sub SetzeSummenformel()
    Dim lngSpalte As Long
    lngSpalte = 5
    With ActiveSheet.UsedRange
      .Parent.Cells(.Offset(.Rows.Count).Row, lngSpalte + 1).Formula = "=SUM(" & .Columns(lngSpalte).Address(0, 0) & ")"
    End With
End Sub

Gruß Uwe

[Furesta]

Danke Gast 123 und danke Uwe für die schnelle Antwort.
Die Lösung von Uwe überfordert mich.
Vielleicht darf ich meine Aufgabe noch mal erläutern - unter Verwendung des Vorschlags von Gast 123::

---> Ich möchte lediglich in der folgenden Summenformel den Wert "17" durch den Wert der Variablen "x" ersetzen, den ich mir in der ersten Zeile ausgerechnet habe.

Sub Makro_sum()
x = ActiveSheet.UsedRange.Rows.Count
    Cells(x + 1, 6).FormulaR1C1 = "=SUM(R[0]C[-1]:R[-17]C[-1])"
End Sub

... das ist alles.
Beste Grüße
Rolf

[ralf_b]

Hallo,

darf ich deinen letzten Post so interpretieren das du meinen Hinweis ebenso nicht verstanden hast?
x= 7
Cells(x + 1, 6).FormulaR1C1 = "=SUM(R[0]C[-1]:R[" & x & "]C[-1])"

ergibt =SUMME(E8:E15)

X darf aber nicht Negativ sein. denn Cells( x+1)  muß immer > 0 sein. Es gibt keine Zeile 0 oder -1 

Hier liegt der Bock eher vor dem "=" Zeichen


gruß 

rb

[Furesta]

SUPER Ralf, vielen Dank.
Die Syntax mit [" & x & "] dem stellt sich mir zwar ziemlich abenteuerlich und kurios dar - aber es funktioniert!

Es muss nur noch "-x" heißen, denn die Werte oberhalb der Einfügestelle der Formel sollen addiert werden:
x = ActiveSheet.UsedRange.Rows.Count
Cells(x + 1, 6).FormulaR1C1 = "=SUM(R[0]C[-1]:R[" & -x & "]C[-1])"
Hinweis: Die erste Zeile ist eine Leerzeile, deswegen Cells(x + 1, 6)
DANK auch an die Betreuer des Forums!
Beste Grüße
Rolf

[ralf_b]

freut mich wenn du der Lösung näher gekommen bist .
Aber  x = -17  und  Cells(x + 1, 6)   
dann wären wir bei Adresse Range("F-16") , oH ,ein Jagdflugzeug   
 
Cells(1) ist im Activesheet  A1  Wenn du Cells nicht zu einem bestimmten Bereich innerhalb des Blattes beziehst, dann dürfte Cells(-16 ,6) im Nirvana landen.  

verständlich? 



gruß rb

[Furesta]

Lieber Ralf,
das ist ein Missverständnis.
"x" selber ist eine positive Zahl, in meinem Beispiel besteht das Datenfeld aus 17 Zeilen.
x = ActiveSheet.UsedRange.Rows.Count
Die Formel wird in Spalte 6 auf Höhe der letzten Zeile eingetragen - eine Leerzeile!.
Cells(x + 1, 6).FormulaR1C1 =
Das "-" muss nur in dem Zellbezug durch "-x" ersetzt werden, damit die Werte in den Reihen oberhalb des Eintrags der Summenformel addiert werden.
=SUM(R[0]C[-1]:R[" & -x & "]C[-1])"
Beste Grüße
Rolf