Rechnen mit nächster nicht-leerer Zelle

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Rechnen mit nächster nicht-leerer Zelle
von: S.
Geschrieben am: 02.09.2015 12:47:50

Hallo zusammen,
ich benötige Hilfe bei folgendem Problem.
Es geht um die Berechnung von Nettoarbeitstagen in Verbindung mit einer Abfrage ob in der entsprechenden Zelle ein Inhalt vorhanden ist und falls nicht, das Suchen der nächsten Zelle, welche ein Datum enthält.
Die Formel soll in einer Zeile die Zellen der Reihe nach auf Inhalt überprüfen und die erste gefüllte Zelle nutzen um die Rechnung auszuführen.
Hier ein Minimalbeispiel für die Problemstellung:
https://www.herber.de/bbs/user/99967.xlsx
Vielen Dank und beste Grüße,
S.

Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: spinifex
Geschrieben am: 02.09.2015 12:56:42
Hallo S.
vielleicht kannst Du Dir folgende Code umstricken, der die letzte belegte Zeile in Spalte 1 findet:

Sub LetzteBelegteZeile()
Cells(65000, 1).End(xlUp).Offset(0, 0).Select
End Sub
Wenn Du statt Cells(6500,1) zB eine For I = 1 to (max. Anzahl Deiner Zeilen)-Schleife mit einer zweiten Schleife For J = 1 to (max Anzahl Deiner Zellen)-Schleife verschachtelst, geht VBA Zeile für Zeile alle Zellen durch
Viel Erfolg
Spinifex

Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: S.
Geschrieben am: 02.09.2015 13:30:24
Hallo Spinifex,
vielen Dank für deinen Vorschlag, nur ich hätte vermutlich noch dazu schreiben müssen, dass ich von VBA keine Ahnung habe.
Daher würde ich das Problem zunächst gern mit einer Formel lösen.
Für Nettoarbeitstage generell nutze ich zZ folgende Formel:

=WENN(ODER(ISTLEER(A1);ISTLEER(B1);B1-A1<0);""
;MAX(0;NETTOARBEITSTAGE(A1+1;B1-1))+(KÜRZEN(A1)<>KÜRZEN(B1))-REST(A1;1)+REST(B1;1))
Nun würde ich eben diese gerne mit der Abfrage des Zelleninhaltes kombinieren.

Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: ... neopa C
Geschrieben am: 02.09.2015 14:28:20
Hallo S,
... hast Du Dir meinen Formelvorschlag von 13:19 schon angeschaut?
Die Formel kannst Du natürlich noch an Deine Verhältnisse anpassen, z.B. so:

=WENN(ISTZAHL(A2)*(ANZAHL(B2:E2)>0);NETTOARBEITSTAGE(A2;VERWEIS(9^9;A2:E2));"")
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit VERWEIS() ...
von: ... neopa C
Geschrieben am: 02.09.2015 13:19:21
Hallo,
... in F2: =NETTOARBEITSTAGE(A2;VERWEIS(9^9;A2:E2;[Freie Tage])) und Formel nach unten kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: spinifex
Geschrieben am: 02.09.2015 15:12:03
Hallo nochmal,
falls Du das ganze allgemein irgendwann doch in VBA lösen willst, hilft Dir villeicht folgende umgestrickte VBA-Fassung.

Sub LetzteBelegteZelle()
'Variablen definieren
Dim intR As Integer
Dim strR As String
'Schleife zum Auffinden der letzten belegten Zeile bei max. 20 Zeilen
    For i = 1 To 20
        Cells(i, 1).End(xlUp).Offset(0, 0).Select
    Next i
'Auslesen der Adresse der gefundenen Zeile
strR = ActiveCell.Address
'Übernehmen der Zeilennummer in die Variable
intR = Right(strR, 1)
'Schleife zum Auffinden der letzten belegten Zelle in dieser Zeile bei max. 10 Spalten
    For i = 1 To 10
        Cells(intR, i).End(xlNext).Offset(0, 0).Select
    Next i
MsgBox "Ab hier kannst Du weiterarbeiten!", , "Ausgewählte Zelle: " & ActiveCell.Address
End Sub
Zum Ausprobieren klicke mit der rechten Maustaste unten links auf den Reiter Deines Arbeitsblatts, klicke Code anzeigen an und kopiere den Code oben dort hinein.
Dann rufst aus einer beliebigen Zelle das Makro LetzteBelegteZelle auf - et voilá!
Viel Spaß!

Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: S.
Geschrieben am: 04.09.2015 09:04:01
Hallo,
vielen Dank für eure Hilfe bisher. Ich habe mich nun doch an VBA herangewagt.
Nur habe ich noch Probleme mit der bereits geposteten Formel für Nettoarbeitstage. Denn diese brauche ich in dezimal-Darstellung. (also angebrochene Tage)
Als Beispiel:
Ausgangsdatum: 04.08.2015 07:45:00 (Zelle: 5;14)
Enddatum:04.08.2015 09:30:00 (Zelle: 5;22)
Durch das Makro erhalte ich in der entsprechenden Nettoarbeitszeitzelle folgende Formel:

=NETTOARBEITSTAGE(INDIREKT(ADRESSE(5;14))+1;INDIREKT(ADRESSE(5;22))-1)
+(KÜRZEN(INDIREKT(ADRESSE(5;14)))<>KÜRZEN(INDIREKT(ADRESSE(5;22))))
-REST(INDIREKT(ADRESSE(5;14));1)+REST(INDIREKT(ADRESSE(5;22));1)
Dies führt zu dem Ergebnis von "-2,93Tage".
Hat jemand vielleicht einen anderen Vorschlag für die Dezimaldarstellung der berechneten Nettoarbeitstage?
Das unterscheiden von leeren/vollen Zellen funktioniert soweit - deswegen poste ich hier noch meinen Lösungsvorschlag.
Dim Zeile As Integer
Dim Spalte As Integer
Dim Zeilenanzahl As Integer
Dim ersteSpalte As Integer
Dim AktSpalte As Integer
Dim Spaltenabstand As Integer
Dim MaxSpalte As Integer
Dim Ausgansdatumsspalte As Integer
Dim Endatumsspalte As Integer
Dim CursorSpalte As Integer
Dim n As Integer
ersteSpalte = 5 ' hier wird der Startpunkt eingestellt, erstes Datum
AktSpalteStart = 12   ' hier wird der Startpunkt eingestellt
Spaltenabstand = 8 ' Abstand zwischen den jeweiligen Datumsspalten
SpaltenabstandLZ = 9 ' Abstand zwischen den jeweiligen Nettoarbeitstagespalten
Zeilenanzahl = Cells(Rows.Count, 1).End(xlUp).Row 'Zahl der Zeilen 
                                                   inkl. Überschrift wird ermittelt
MaxSpalte = 104 'Letzte Spalte in der gesucht werden soll
For Zeile = 4 To Zeilenanzahl
    
 For CursorSpalte = AktSpalteStart To MaxSpalte Step SpaltenabstandLZ
  'LZ ermitteln:
    If Cells(Zeile, CursorSpalte - Spaltenabstand + 1).Value <> "" Then
    Ausgansdatumsspalte = CursorSpalte - Spaltenabstand + 1
    Else:
    Ausgansdatumsspalte = 0
    End If
 
 For n = CursorSpalte To MaxSpalte Step SpaltenabstandLZ
    If Cells(Zeile, n + 1).Value <> "" Then
    Endatumsspalte = n + 1
    Exit For
    Else:
    Endatumsspalte = 0
    End If
    Next n
    
  If Ausgansdatumsspalte <> 0 And Endatumsspalte <> 0 Then
    If Cells(Zeile, Endatumsspalte).Value - Cells(Zeile, Ausgansdatumsspalte).Value >= 0 Then 
                   ' wenn links und rechts Zellen gefunden und Diffenz der Daten nicht negativ
        Cells(Zeile, CursorSpalte).FormulaLocal = "=NETTOARBEITSTAGE
                   (INDIREKT(ADRESSE(" & Zeile & ";" & Ausgansdatumsspalte & "))+1;
                    INDIREKT(ADRESSE(" & Zeile & ";" & Endatumsspalte & "))-1)
                   +(KÜRZEN(INDIREKT(ADRESSE(" & Zeile & ";" & Ausgansdatumsspalte & ")))
                   <>KÜRZEN(INDIREKT(ADRESSE(" & Zeile & ";" & Endatumsspalte & "))))
                   -REST(INDIREKT(ADRESSE(" & Zeile & ";" & Ausgansdatumsspalte & "));1)
                   +REST(INDIREKT(ADRESSE(" & Zeile & ";" & Endatumsspalte & "));1)"
    Else: Cells(Zeile, CursorSpalte).Value = 0
    End If
  Else: Cells(Zeile, CursorSpalte).Value = ""
    End If
 
 Next CursorSpalte
Next Zeile


Bild

Betrifft: AW: Rechnen mit nächster nicht-leerer Zelle
von: S.
Geschrieben am: 04.09.2015 09:48:57
...ich hatte vergessen den Haken für "ungelöstes Problem" zu aktivieren.

Bild

Betrifft: Nettoarbeitstage dezimal
von: S.
Geschrieben am: 04.09.2015 14:53:32
...Und jetzt habe ich herausgefunden, dass es die Funktion networkday() auch in VBA gibt.
Der Code ändert sich damit wie folgt, nur habe ich nun noch das Problem, dass ich nicht ganze Tage, sondern die genaue Zeitangabe ausgegeben haben möchte. (wie z.B. 1,37Tage)

Dim Zeile As Integer
Dim Spalte As Integer
Dim Zeilenanzahl As Integer
Dim ersteSpalte As Integer
Dim AktSpalte As Integer
Dim Spaltenabstand As Integer
Dim MaxSpalte As Integer
Dim Ausgansdatumsspalte As Integer
Dim Endatumsspalte As Integer
Dim CursorSpalte As Integer
Dim n As Integer
Dim dStart As Variant
Dim dEnd As Variant
ersteSpalte = 5 ' hier wird der Startpunkt eingestellt, erstes Datum
AktSpalteStart = 12   ' hier wird der Startpunkt eingestellt, erste Liegezeitermittlung
Spaltenabstand = 8 ' Abstand zwischen den jeweiligen Datumsspalten
SpaltenabstandLZ = 9 ' Abstand zwischen den jeweiligen Liegezeitenspalten
Zeilenanzahl = Cells(Rows.Count, 1).End(xlUp).Row 'Zahl der Zeilen inkl. Überschrift wird  _
ermittelt
MaxSpalte = 104 'Letzte Spalte in der gesucht werden soll
For Zeile = 4 To Zeilenanzahl
    
 For CursorSpalte = AktSpalteStart To MaxSpalte Step SpaltenabstandLZ
  'LZ1 ermitteln:
    If Cells(Zeile, CursorSpalte - Spaltenabstand + 1).Value <> "" Then
    Ausgansdatumsspalte = CursorSpalte - Spaltenabstand + 1
    dStart = Cells(Zeile, Ausgansdatumsspalte).Value
    Else:
    Ausgansdatumsspalte = 0
    End If
 
 For n = CursorSpalte To MaxSpalte Step SpaltenabstandLZ
    If Cells(Zeile, n + 1).Value <> "" Then
    Endatumsspalte = n + 1
    dEnd = Cells(Zeile, Endatumsspalte).Value
    Exit For
    Else:
    Endatumsspalte = 0
    End If
    Next n
  If Ausgansdatumsspalte <> 0 And Endatumsspalte <> 0 Then
    If Cells(Zeile, Endatumsspalte).Value - Cells(Zeile, Ausgansdatumsspalte).Value >= 0 Then 
                ' wenn links und rechts Zellen gefunden und Diffenz der Daten nicht negativ
       Cells(Zeile, CursorSpalte).Value = Application.WorksheetFunction.NetworkDays(dStart,  _
dEnd)
             
    Else: Cells(Zeile, CursorSpalte).Value = 0
    End If
  Else: Cells(Zeile, CursorSpalte).Value = ""
    End If
 
 Next CursorSpalte
Next Zeile


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Rechnen mit nächster nicht-leerer Zelle"