Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1360to1364
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

For Each Rng in RngSumme - Felder ausschliessen

For Each Rng in RngSumme - Felder ausschliessen
09.05.2014 10:53:43
Peter
Guten Tag
Ich habe mir eine Function gebastelt, die mir innerhalb des übergebenen Ranges alle Zahlen addiert (natürlich ohne die Zelle, aus der die Funktion aufgerufen wird) und die Summe als negativen Wert zurück gibt.
Beispiel:
A1: 1
A2: 2
A3: 3
A4: =psSumX(A1:A5) --> -19
A5: 13
Die Summe von A1:A5 ergibt NULL (und ist so gewollt).
Nun wollte ich noch verhindern, dass mir ein Eintrag, der nicht eine Zahl ist oder ein Fehlerwert die Funktion zu Fall bringt.
Also habe ich die entsrpechenden Abfragen gemacht:
If Left(rng.Text, 1) "#" Then
If IsNumeric(rng.Value) Then
Wenn diese Bedingungen nicht zutreffen, soll die nächste Zelle abgearbeitet werden.
Was mich nun irritiert ist, dass mit dieser Erweiterung, die ich nachstehend wieder auskommentiert habe, ein Zirkelbezug resultiert.
Was ist da falsch?
Gruss, Peter
Public Function psSumX(rngSumme As Range)
'' errechnet aus einem Bereich die Summe mit einem negativen Vorzeichen
'' so dass die Bereichssumme und der ermittelte Wert NULL ergibt
'' der Funktionsaufruf kann innerhalb des Bereiches stehen (bewirkt kein Zirkelbezug)
'' als Range kann ein Bezug eingegeben werden oder
'' der definierte Name (ohne Anführungszeichen)
Dim rngCaller As Range, rng As Range
Set rngCaller = Application.Caller
psSumX = 0
For Each rng In rngSumme
''If Left(rng.Text, 1)  "#" Then
''If IsNumeric(rng.Value) Then
If rngCaller.Address(0, 0)  rng.Address(0, 0) Then
psSumX = psSumX - rng.Value
Else
End If
''End If
''End If
Next
End Function

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: For Each Rng in RngSumme - Felder ausschliessen
09.05.2014 11:08:08
Daniel
Hi
entweder die Summenfunktion statt der Subtraktion verwenden, weil die Summenfunktion Texte ignoriert:
psSumX = Worksheetfunction.Sum(psSumX, rng.Value)

und am Schluss dann das ganze negativ machen:
psSumX = -psSumX

oder das ganze in On Error Resume Next kapseln, dann läuft die Schleife bei einem Fehler einfach weiter und diese Zelle wird übersprungen:
On Error Resume Next
psSumX = psSumX - rng.Value
On Error Goto 0

Gruß Daniel

Anzeige
Zirkelbezug
09.05.2014 11:22:17
Erich
Hi Peter,
mit rng.Text und rng.Value prüfst du auch den Wert der Zelle A4 ab, die gerade zu berechnen ist,
daher der Zirkelbezug.
Die Prüfung der Adresse mit rng.Address ist unschädlich.
Probier mal

For Each rng In rngSumme
If rngCaller.Address(0, 0)  rng.Address(0, 0) Then
If Left(rng.Text, 1)  "#" Then
If IsNumeric(rng.Value) Then psSumX = psSumX - rng.Value
End If
End If
Next
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Zirkelbezug - Ergänzung
09.05.2014 11:26:39
Erich
Hi Peter,
das sollte schon ausreichen:

For Each rng In rngSumme
If rngCaller.Address(0, 0)  rng.Address(0, 0) Then
If IsNumeric(rng.Value) Then psSumY = psSumY - rng.Value
End If
Next
"#" ist eh nicht numerisch...
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Zirkelbezug - Ergänzung
09.05.2014 11:45:39
Peter
Hallo Daniel und Erich
Vielen Dank für die Antworten.
Deine Antwort Erich hat mir die Lösung gebracht, vielen Dank.
Nun habe ich allerdings gesehen, dass ich die Zellen, die zu addieren sind, weiter einschränken muss.
Wenn ich beispielsweise in einer Zelle des Bereichs eine Zahl mit vorangestelltem Hochkomma eingebe (z.B. '20140331), sollte diese nicht mit in die Summe gerechnet werden. Wie muss die Prüfung erfolgen, ob es sich um eine "echte" Zahl handelt?
Gruss, Peter

AW: Zirkelbezug - Ergänzung
09.05.2014 11:51:15
Daniel
Hi
ob eine Zahl oder ein Text vorliegt, kannst du mit der Funktion VarType ermitteln.
If VarType(rng.Value) = 5 Then psSumX = psSumX - rng.Value
oder du verwendest wie schon von mir vorgeschlagen Worksheetfunction.Sum, weil Worksheetfunction.Sum Texte ignoriert (auch wenn diese wie Zahlen aussehen).
Gruß Daniel

Anzeige
AW: Zirkelbezug - Ergänzung
09.05.2014 12:03:54
EtoPHG
Hallo Peter,
Vartype ist zu empfehlen, Worksheetfunction.Sum hingegen nicht, weil Fehlerwerte zu Fehler führen und Datumswerte mitaddiert würden.
Darum:
Public Function psSumX(rngSumme As Range)
Dim rC As Range
For Each rC In rngSumme
If Application.Caller.Address  rC.Address Then
If VarType(rC) = vbDouble Then psSumX = psSumX - rC
End If
Next
End Function
Gruess Hansueli

AW: Zirkelbezug - Ergänzung
09.05.2014 12:06:13
Peter
Hallo zusammen
Vielen Dank für alle Antworten.
Jetzt läufts perfekt!
Gruss, Peter

psSumX statt psSumY (owT)
09.05.2014 11:27:53
Erich

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige