Summenformel in vba ?
Gerd Döring
Ich füge in meinem vba makro eine Formel (Summe) ein.
Dort möchte ich den Bereich für die Summe automatisch übernehmen, so wie
automatisch der Bereich markiert wird wenn man auf das Summensymbol
klickt.
Ich hatte das mit UsedRange probiert, nun stelle ich aber fest, dass
nicht nur die betreffende Spalte sondern auch die Spalten neben meiner
aktiven Zelle zu diesem Bereich gehören. In meine Summe soll aber nur
der benutzte Bereich der betreffenden Spalte gehören.
Weiß jemand wie das geht ? Wenn man in den Tabellen auf das
Summensymbol klickt wird doch auch nur die jeweilige Spalte oder Zeile
automatisch herausgesucht.
Gerd
Bernd Held
wie gefällt Dir folgende Lösung?
Sub AktiveZelleKopieren()
Dim n As Range
Dim s As String
s = ActiveCell.Address
Set n = Application.InputBox _
("Wählen Sie den Bereich an den Sie summieren möchten", Type:=8)
Range(s).Select
ActiveCell.Formula = "=Sum(" & n.Address & ")"
End Sub
Über eine Pinputbox den gwünschten Bereich flexibel markieren und dann
automatisch den markierten Bereich summieren lassen.
Viele Grüße
Bernd
----------------------------------------------------
Die MacHero EXCEL Homepage
http://members.aol.com/Machero
Excel-Tipps&Tricks, VBA, Publikationen, Galerie und mehr...
----------------------------------------------------
--
_____________________________________________________________
NewsGroups Suchen, lesen, schreiben mit http://netnews.web.de
Gerd Döring
Danke Bernd
Das ist ja ersteinmal eine gute Lösung.
Da ich aber 150 - 200 solche Summen einfügen muß ist das trotz dieser
erleichterung ein ganz schöner Aufwand.
Anders gesagt: Wenn ich die Zellen wo die Summe reinkommen soll per Hand
auswähle und auf das Summensymbol klicke, habe ich ja nur ein Klick zu
machen.(der Bereich wird ja richtig ausgewählt) und bin wahrscheinlich
schneller am Ziel
Ich werde es so machen: Die Zellen über der Zelle wo die Summe reinkommen
soll so weit abfragen bis ich auf eine leere Zelle stoße und dann diesen
Bereich übergeben. Ich dachte nur dafür gibt es eine Eigenschaft wie in etwa
UsedRange.Column oder UsedColumn oder so etwas.
Vielleicht hast Du ja noch einen anderen Lösungsvorschlag ?
tschau Gerd
Bernd Held schrieb:
Bernd Held
wie gefällt Dir der folgende Vorschlag?
Sub AktiveZelleSpalteRunter()
Dim s As String
s = Range("A65536").End(xlUp).Address
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Formula = "=Sum(" & "A1" & ":" & s & ")"
End Sub
Viele Grüße
Bernd
----------------------------------------------------
Die MacHero EXCEL Homepage
http://members.aol.com/Machero
Excel-Tipps&Tricks, VBA, Publikationen, Galerie und mehr...
----------------------------------------------------
--
Gerd Döring
Leider kann ich auf die schnelle mit .end(xlUp) nichts anfangen ich beschäftige
mich erstmal damit. Danke
Für den Fall, daß wir aneinander vorbeigeschrieben haben, hier nochmal mein
Anliegen.
Ich habe eine Zelle z.B. "B20" in den Zellen "B10" bis "B19" stehen Zahlen drin
(auch 0 !)
in die Zelle "B20" möchte ich jetzt mittels vba die Formel =Summe (B10:B19)
einfügen. Da in den Zellen "B8" usw. auch Zahlen drinstehen können die aber in
meine Summe nicht reinzählen dürfen, muß ich den Bereich von B10 - B19 erkennen
lassen das mache ich damit:
Beispiel für B20:
Zeile=20
Zeileschleife = Zeile-1
Do While Not IsEmpty(Range("b" & Zeileschleife))
Zeileschleifel = Zeileschleife - 1
Loop
Range("B" & Zeile).Formula = "=sum(b" & Zeile - 1 & ": b" & Zeileschleife & ")"
Hier dachte ich gibt es eine Möglichkeit diese Schleife zu ersetzen.
tschau und danke für die Mühe, Gerd
Bernd Held schrieb:
> Hallo Gerd,
Jörg Nissen
"Gerd Döring" < gerd.d...@hqde.infrabrk.com> schrieb im Newsbeitrag
news:3A2F986E...@hqde.infrabrk.com...
: Leider kann ich auf die schnelle mit .end(xlUp) nichts anfangen ich
beschäftige
: mich erstmal damit. Danke
:
: Für den Fall, daß wir aneinander vorbeigeschrieben haben, hier nochmal
mein
: Anliegen.
:
: Ich habe eine Zelle z.B. "B20" in den Zellen "B10" bis "B19" stehen
Zahlen drin
: (auch 0 !)
: in die Zelle "B20" möchte ich jetzt mittels vba die Formel =Summe
(B10:B19)
: einfügen. Da in den Zellen "B8" usw. auch Zahlen drinstehen können die
aber in
: meine Summe nicht reinzählen dürfen, muß ich den Bereich von B10 - B19
erkennen
: lassen das mache ich damit:
:
: Beispiel für B20:
:
: Zeile=20
: Zeileschleife = Zeile-1
: Do While Not IsEmpty(Range("b" & Zeileschleife))
: Zeileschleifel = Zeileschleife - 1
: Loop
: Range("B" & Zeile).Formula = "=sum(b" & Zeile - 1 & ": b" & Zeileschleife
& ")"
:
: Hier dachte ich gibt es eine Möglichkeit diese Schleife zu ersetzen.
zeichne doch mit dem Makrorekorder, während Du auf der Zelle B20 stehst, die
Eingabe <STRG>-<SHIFT>-<CURSOR-UP> auf. Genau das ist der Befehl, der Dir
die Schleife ersetzt.
mfg
Jörg Nissen
Gerd Döring
> Eingabe <STRG>-<SHIFT>-<CURSOR-UP> auf. Genau das ist der Befehl, der Dir
> die Schleife ersetzt.
>
> mfg
> Jörg Nissen
Ja genau so etwas in der Art habe ich gesucht. Nur wie bekomme ich diesen Befehl
mit dem Makrorekorder heraus ? Im Macrocode wird mir doch nur
Range("b19:b10").Select angezeigt. Da könnte ich ja gleich den Klick auf das
Summensymbol aufzeichnen.
Bei dieser Tastenkombination muß ich aber auf der Zelle C19 (also in dem Bereich
drin) stehen und nicht in der Zelle in der die Summenformel rein soll. Aber
damit kann ich zur Not leben.
Danke Jörg
tschau Gerd
Gennaro della Rocca
heute habe ich ein bißchen experimentiert und bin zu den untenstehenden
Skript gekommen.
Es ist etwas umfangreich aber dafür kannst Du auf ein mal so viele Summen
bilden wie es Dir
gefällt. Die einzige Voraussetzung dafür ist, dass alle ersten Zellen eines
jeden Bereichs, von
oben angefangen sich in die gleiche Zeile befinden (z.B.: A10: A45,
F10:F123, AA10:AA3456 etc.)
Vielleicht gefällt Dir diese Lösung deines Problems
Gruss
Gennaro della Rocca
' **************************************************
' **************************************************
' Autor: Gennaro della Rocca
' Scriptname: Summenbilden
' Datum: 08.12.2000
' **************************************************
' **************************************************
Option Explicit
' **************************************************
' WICHTIGER HINWEIS:
' Diese Prozedur setzt voraus, dass sich alle Werte
' der ersten Zellen sich in der gleichen Zeile be-
' finden. Der von mir eingetragene Range-Bereich
' "A10" sollte von Dir angepasst werden
' **************************************************
Sub SummenBilden()
Dim ws As Worksheet
Dim rn As Range
Dim varI As Variant
Dim avarFeld()
Dim intI As Integer
' **************************************************
' Zählervariable initialisieren
' **************************************************
intI = 0
' **************************************************
' Zeile 10 (ändern!!) als Bereich in der
' Variablen "rn" speichern
' **************************************************
Set rn = Range("a10").EntireRow
' **************************************************
' Die Zeile wird nun auf Zellen übeprüft, die
' nicht leer sind. Ist die Zelle nicht leer, dann
' wird der Wert der Zählervariablen "intI" um 1
' erhöht, die Feldvariable "avarFeld()" dauerhaft
' redimensioniert und die Zellenadresse zum
' korrespondierenden Feld (intI) hinzugefügt
' **************************************************
For Each varI In rn.Cells
If varI <> "" Then
intI = intI + 1
ReDim Preserve avarFeld(intI - 1)
avarFeld(intI - 1) = varI.Address
End If
Next
' **************************************************
' Verweis auf "Tabelle1" in der Variablen "ws"
' speichern
' **************************************************
Set ws = Sheets("Tabelle1")
' **************************************************
' Die For-Next-Schleife durchläuft die datenfeld-
' variable und löscht zuerst alle benannten
' Bereiche, die mit "Daten" anfangen, dann löscht
' alle Summen-Formeln (sonst werden sie mitaddiert!)
' jeweiligen Bereichs. Anschliessend werden die
' Bereiche neu benannt ("Daten" & cstr(intI)) und
' die Summen-Formel neu erstellt.
' **************************************************
For intI = 0 To UBound(avarFeld())
' Fehlermeldungen ignorieren
On Error Resume Next
' Benannter Bereich wird gelöscht (falls vorhanden)
ActiveWorkbook.Names("Spalte" & CStr(intI)).Delete
' Fehlermeldungen reaktivieren
On Error GoTo 0
' Verweis auf Adresse in der Feldvariablen avarFeld()
' in der Variablen "rn" speichern
Set rn = Range(avarFeld(intI))
' Falls Formel bereits vorhanden wird diese gelöscht
If rn.End(xlDown).HasFormula = True Then
rn.End(xlDown).ClearContents
End If
' Verweis auf Rangebereich (z.B. "A1" nach unten bis Ende)
' in der Variablen "rn" speichern
Set rn = Range(avarFeld(intI), _
Range(avarFeld(intI)).End(xlDown))
' Bereich neu benennen
rn.Name = "Spalte" & CStr(intI)
' Formel zuweisen
rn.End(xlDown).Offset(1, 0).FormulaR1C1 = _
"=Sum(Spalte" & CStr(intI) & ")"
' Ende der For-Next-Schleife
Next
' Ende der Prozedur
End Sub
Jörg Nissen <joerg....@t-online.de> schrieb in im Newsbeitrag:
90q42k$h4v$02$3...@news.t-online.com...
> Hallo Gerd,
>
> "Gerd Döring" < gerd.d...@hqde.infrabrk.com> schrieb im Newsbeitrag
> news:3A2FAEAB...@hqde.infrabrk.com...
> : Ja genau so etwas in der Art habe ich gesucht. Nur wie bekomme ich
diesen
> Befehl
> : mit dem Makrorekorder heraus ? Im Macrocode wird mir doch nur
> : Range("b19:b10").Select angezeigt. Da könnte ich ja gleich den Klick auf
> das
> : Summensymbol aufzeichnen.
> : Bei dieser Tastenkombination muß ich aber auf der Zelle C19 (also in dem
> Bereich
> : drin) stehen und nicht in der Zelle in der die Summenformel rein soll.
> Aber
> : damit kann ich zur Not leben.
>
> mein Makrorekorder liefert mir folgendes Ergebnis, wenn Zelle B20 leer ist
> und in den Zellen B10:19 sowie B1:B8 Werte stehen:
>
> Selection.End(xlUp).Select
> Range(Selection, Selection.End(xlUp)).Select
>
> Angepaßt auf Deine Anwendung müßte es dann etwas so aussehen:
>
> ActiveCell.Offset(-1, 0).Select
> Selection.Offset(1, 0).Formula = "=Sum(" & _
> Range(Selection, Selection.End(xlUp)).Address(False, False) & ")"
>
>
> mfg
> Jörg Nissen
>
>