✔ Formel funktioniert in Spalte "Z" und "AB" aber nicht in "AA"

[Joerg66]

Hallo,
ich habe folgende Matrix-Formel, die darunterliegende Werte bis zum nächsten Leerzeichen addieren soll. Das klapp auch, in allen Spalten, aber nicht in "AA". Hab schon Zeichen für Zeichen kontrolliert, aber ich kann den Fehler nicht finden. Ob mir wohl jemand sagen kann, wo ich nicht richtig hinschauhe ?
Spalte Z:
{=SUMME(INDIREKT("Z"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):INDIREKT("Z"&MIN(WENN(INDIREKT("Z"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$Z$8000="";ZEILE(INDIREKT("Z"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$Z$8000)))))}

Spalte AA:
{=SUMME(INDIREKT("AA"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):INDIREKT("AA"&MIN(WENN(INDIREKT("AA"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$AA$8000="";ZEILE(INDIREKT("AA"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$AA$8000)))))}

Spalte AB
{=SUMME(INDIREKT("AB"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):INDIREKT("AB"&MIN(WENN(INDIREKT("AB"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$AB$8000="";ZEILE(INDIREKT("AB"&VERGLEICH(INDIREKT("A"&ZEILE());$A$1:$A$8000)+1):$AB$8000)))))}

 

[Yaslaw]

Kannst du mal eine Beispieldatei hochladen?
Es ist schwer selber ein Testfall zum Prüfen einer Formel zu erstellen, wenn man keine Ahnung hat, was in der Zeile stehen sollte.

ggf. ist der Fehler auch nicht in der Formel sondern in den Daten.

Und wie zeigt dich der Fehler?

 

[Joerg66]

Aber klar.
In Spalte "A" steht nur eine (Auftrags)Nummer. In den Spalten "Z" und "AA" stehen unter den blauen Feldern einzelne Werte, die dann in den Feldern darüber (blau)addiert werden. Das war schon alles. An den Zeilennummern kann sich nur sehr viel ändern, wenn z.B. Auftrag 1 noch eine Zeile hinzu bekommt, deswegen dieser Aufwand. Der Fehler ist das #NV

 

[Yaslaw]

Ich würde eine eigene Funktion in VBA schreiben, die dasselbe tut

Public Function mySum() As Long
    Const C_AUFTRAGS_NR_COL = 1
   
    'Worksheet auslesen
    Dim ws As Worksheet:        Set ws = ActiveSheet
   
    Dim auftrag As Range:       Set auftrag = ws.Cells(Application.Caller.Row, C_AUFTRAGS_NR_COL)
    Dim ersterWert As Range:    Set ersterWert = Application.Caller
   
    Dim delta As Long:          delta = 1
   
    'Nächste Zeile durchiterieren, bis entweder der Auftrag nicht mehr lehr ist oder das Feld in der aktuellen Spalte  leer ist (EOF)
    Do While auftrag.Offset(delta).Value = Empty And ersterWert.Offset(delta).Value <> Empty
        delta = delta + 1
    Loop
   
    'Summe auf den Bereich anwedenden
    mySum = WorksheetFunction.Sum(auftrag.Offset(1), ersterWert.Offset(delta - 1))
End Function

Dannn einfach in der Zelle "=mySum()" eintragen und fertig



Nachtrag: Habe die Funktion ausgewechselt. über Offset() ist noch eleganter

 

[Joerg66]

Wow, ist ja cool, vielen Dank für Deine Mühe.
Ich wußte bisher gar nicht, das man eigene Funktionen einfach aus einer Zelle heraus aufrufen kann ... hihi
Aber das ist schon echt 1000 Mal eleganter als meine undurchdringliche Formel.
Werde ich gleich morgen mal testen .... hab schon Feierabend

 

[Yaslaw]

Application.Caller gibt die Referenz auf die aufrufende Zelle.

 

[Joerg66]

Guten Morgen,
habe jetzt in dem VBAProject ein Modul1 und darin Deine Funktion eingefügt. Doch es wird mir immer nur der unterste Wert der Liste darunter angezeigt, diese aber nicht addiert. .... das müßte ich aber hinbekommen.
Außerdem fehlt mir der Automatische Aufruf. Bei mir ist es so, das die Berechnung erst ausgeführt wird, wenn ich die Zelle mit dem Funktionsaufruf anwähle und "Enter" drücke.
Ein Worksheet -> Change und darin

Private Sub Worksheet_Change(ByVal Target As Range)
Call mySum
End Sub

brachte auch nicht das gewünschte Ergebnis.
Immer diese lästigen Details )

 

[Joerg66]

Was ich nicht kapiere, woher weiß die Funktion in welcher Spalte sie aufgerufen wird ....

 

[Yaslaw]

Stimmt, da sind noch einige Fehler drin.
Das Aktualisieren scheint mit dem Application.Caller nicht zu funktionieren

Zudem war die Summenberechnung falsch.

Dann muss man halt auf das klassische zurück.
Versuchs mal damit

Public Function mySum1(ByRef iAuftagRow As Range, ByRef iCalcColumn As Range) As Long
    Const C_AUFTRAGS_NR_COL = 1
  
    'Worksheet auslesen
    Dim ws As Worksheet:        Set ws = ActiveSheet
  
    Dim auftrag As Range:       Set auftrag = iAuftagRow(1)
    Dim ersterWert As Range:    Set ersterWert = ws.Cells(iAuftagRow.Row, iCalcColumn.Column)
  
    Dim delta As Long:          delta = 1
  
    'Nächste Zeile durchiterieren, bis entweder der Auftrag nicht mehr lehr ist oder das Feld in der aktuellen Spalte  leer ist (EOF)
    Do While auftrag.Offset(delta).Value = Empty And ersterWert.Offset(delta).Value <> Empty
        delta = delta + 1
    Loop
  
    'Summe auf den Bereich anwedenden
    If delta = 1 Then
        'Keine Daten vorhanen
        mySum1 = 0
    Else
        mySum1 = WorksheetFunction.Sum(ws.Range(ersterWert.Offset(1), ersterWert.Offset(delta - 1)))
    End If
End Function

Folgende Aufrufe müssten funktionieren

=MySum1(5:5;Z:Z)
=MySum($A5;Z:Z)
=MySum($A5;$Z$1)

 

[Joerg66]

Hat so nicht ganz funktioniert, irgendwas mit "falscher Datentyp" war der Fehler. Hab das jetzt so geändert:

Public Function mySum(iAuftagRow As Long, iCalcColumn As Long) As Long
    'Worksheet auslesen
    Dim ws As Worksheet:        Set ws = ActiveSheet
    Dim auftrag As Range:       Set auftrag = ws.Cells(iAuftagRow, 1)
    Dim ersterWert As Range:    Set ersterWert = ws.Cells(iAuftagRow, iCalcColumn)
    Dim delta As Long:          delta = 1
    'Nächste Zeile durchiterieren, bis entweder der Auftrag nicht mehr lehr ist oder das Feld in der aktuellen Spalte  leer ist (EOF)
   Do While auftrag.Offset(delta).Value = Empty And ersterWert.Offset(delta).Value <> Empty
        delta = delta + 1
  Loop
    'Summe auf den Bereich anwedenden
   If delta = 1 Then
        'Keine Daten vorhanen
       mySum = 0
    Else
        mySum = WorksheetFunction.Sum(ws.Range(ersterWert.Offset(1), ersterWert.Offset(delta - 1)))
    End If
End Function

Der Aufruf un der Zelle lautet dann:

=mySum(ZEILE();SPALTE())

Hab aber immer noch ein Verständnisproblem mit der Automatik, wie das dann gehen soll.
Wenn ich in der Zelle meine Formel stehen habe, also mein ganz erster Ansatz, wird die ja bei einer Änderung sofort aktualisiert.
Mit dieser Funktion jetzt müßte ich bei 100 Aufträgen und um 30 Spalten eine Schleife basteln, die dann 100x30=3000 mal bei jeder Änderung diese Formel aufruft ....... ich blamier mich grad oder?