Sub IntelligenteSummenFunktion()
ActiveCell.FormulaR1C1 = "=SUM(R" & _
ActiveCell.EntireColumn.Find(what:="=SUM(", LookIn:=xlFormulas, lookat:=xlPart, _
searchdirection:=xlPrevious).Row + 1 & "C:R" & ActiveCell.Offset(-1).Row & "C)"
End Sub
Gruß, NoNet
PS : Voraussetzung : Es steht auch irgendwo oberhalb der aktuellen Zelle eine =SUMME()-Funktion und zar nicht direkt in der Zeile über der aktuellen Zelle !
AW: Intelligenter Einzeiler...mit Voraussetzungen
Peter
Hallo NoNet
Vielen Dank auch das klappt!
Kleine Nachfrage: Kann ich den Code anpassen, so dass ich keine absolute Bezüge ($) mehr habe?
Gruss, Peter
Intelligente Summe - relative Zellbezüge
NoNet
Hallo Peter,
klaro geht das :
| VBA-Code: | Sub IntelligenteSummenFunktion()
ActiveCell.FormulaR1C1 = "=SUM(R[-" & ActiveCell.Row - 1 - _
ActiveCell.EntireColumn.Find(what:="=SUM(", LookIn:=xlFormulas, lookat:=xlPart, _
searchdirection:=xlPrevious).Row & "]C:R[-1]C)"
End Sub
Gruß, NoNet
AW: Intelligente Summe - relative Zellbezüge
Peter
Hallo NoNet
Ich bin beeindruckt - es funktioniert! allerdings bin ich daran gescheitert, zu verstehen, wie du das angestellt hast.
Könntest du mir diesen "Einzeiler" erläutern? Das wäre super.
Gruss, Peter
Erklärungsversuch des Einzeilers
NoNet
Hey Peter,
ich versuche es mal zu erklären : Dieser "Einzeiler" vereint 2 Excel-Funktionalitäten in einer :
- Zelle mit SUMME()-Funktion in der gleichen Spalte oberhalb der aktiven Zelle suchen
- In der Zelle oberhalb der aktiven Zelle eine weitere SUMME()-Funktion eintragen
Sub IntelligenteSummenFunktion()
ActiveCell.FormulaR1C1 = "=SUM(R[-" & ActiveCell _
font> .Row - 1 - _
ActiveCell.EntireColumn.Find(what:="=SUM(", _
LookIn:=xlFormulas, lookat:=xlPart, _
searchdirection:=xlPrevious).Row & "]C:R[-1]C)"
End Sub
Mit ActiveCell.EntireColumn.Find(what:="=SUM(", LookIn:=xlFormulas, lookat:=xlPart, searchdirection:=xlPrevious).Row
wird zunächst einmal (ausgehend von der aktuellen Zelle) in der gleichen Spalte rückwärts (daher Searchdirection:=xlPrevious) die Funktion =SUMME(...) (engl.: =SUM(...)) gesucht.
Da es sich bei =SUMME( nur um einen Teil der in der Zelle stehenden Funktion handelt, ist hier das Argument lookat:=xlPart angegeben. Aus der gefundenen Zelle wird die Zeilennummer (.Row z.B. Zeile 10) ermittelt.
Wenn die aktuelle Zelle D20 ist, muss also die Funktion =SUMME(D11:D19) eingetragen werden.
In VBA werden Funktionen mit .Formula (oder .FormulaR1C1) in die Zelle geschrieben (der Funktionsname muss dann in englischer Syntax erfolgen, daher =SUM( und nicht =SUMME( !).
Wie in Excel gibt es aber auch in VBA die Möglichkeit, mit relativen und absoluten Zellebezügen zu arbeiten. Die Zellbezüge werden durch .FormulaR1C1 in der Z1S1-Schreibweise (siehe auch "Extras - Optionen - Allgemein : Z1S1-Bezugsart") angegeben, das erleichtert die Erstellung der relativen Zelladressierung.
Mehr zur R1C1-Darstellung siehe hier :
http://spotlight-wissen.de/lisp/pages/messages/amse-1136883848-12627
).
Die Zeilennr. 11 inder Funktion =SUMME(D11:D19) ergibt sich aus der Zeilennr. der gefundenen SUMME()-Funktion (Zeile 10, s.o.)+1.
Um von der aktuellen Zelle (D20) aus die Zeile 11 zu referenzieren, muss man also 9 Zeilen "nach oben", das errechnet sich aus der aktuellen Zeile - gefundene Zeile (10) - 1.
Die Summe soll bis zur Zelle direkt oberhalb der aktuellen Zelle gebildet werden, also aktuelle Zeile -1, daher R[-1].
Per VBA wird also diese Funktion in die Zelle geschrieben : =SUM(R[-9]C:R[-1]C)
Das ist die Z1S1-Schreibweise für die Funktion mit den relativen Zellebezügen =SUMME(Z(-9)S:Z(-1)S) - ergibt (wenn in D20 eingetragen) die "lesbare" Funktion =SUMME(D11:D19) !
Ich hoffe, das war einigermassen verständlich ;-) kann aber auch gut sein, dass Du dir das nochmal durch Pansen, Netzmagen, Blättermagen, Labmagen und den Kopf gehen lassen musst, um das zu verstehen ;-)
Gruß, NoNet
AW: Erklärungsversuch des Einzeilers
Peter
AW: Intelligente Summe - relative Zellbezüge
Peter
Hallo NoNet
Ich muss nochmals eine Frage zu deinem Code stellen.
Ich habe bemerkt, dass dieser nur nur funktioniert, wenn in der gleichen Spalte nicht schon weiter unten eine SUMMENFORMEL steht:
Wenn ich in Zelle D130 stehe und die vorige Summenformel in D120 steht funktioniert das, wenn nicht nach D130 auch schon eine Summenformel steht.
Steht beispielsweise bereits in Spalte D140 eine Summenformel, würde als Formel D129:D141 eingesetzt, was nicht funktioniert.
Kann man die Suchfunktion abändern, damit nicht von zunterst der Spalte (also die ganze Spalte), sondern von der aktiven Zelle nach oben gesucht wird?
Gruss, Peter
Intelligente Summe : nur Summen OBERHALB
NoNet
Hallo Peter,
habe Deine Frag erst jetzt gelesen. Klaro geht das :
Sub IntelligenteSummenFunktion()
ActiveCell.FormulaR1C1 = "=SUM(R[-" & ActiveCell.Row - 1 - _
Range(Cells(1, ActiveCell.Column), ActiveCell.Offset(-1)).Find( _
what:="=SUM(", LookIn:=xlFormulas, lookat:=xlPart, _
searchdirection:=xlPrevious).Row & "]C:R[-1]C)"
End Sub
Gruß, NoNet
AW: Intelligente Summe : nur Summen OBERHALB
Peter
Hallo NoNet
Vielen Dank. Ich habe wieder dazu gelernt.
Gruss, Peter
|
|