Wenn Zelle leer - Datum prüfen und vergleichen

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

Betrifft: Wenn Zelle leer - Datum prüfen und vergleichen
von: reiner
Geschrieben am: 23.09.2015 08:46:46

Guten Morgen zusammen,
Ich habe ein Tabellenblatt mit dem ich ein Datum überwachen will.
Folgende Spalten gibt es :
Datum, Name, Betrag, Brief1, Brief2, Zahlung erfolgt
Das Makro soll jetzt folgendes prüfen :
Wenn die Zelle bei „ Zahlung erfolgt „ leer ist,
prüfe ob in Zelle bei „ Brief2 „ ein Datum steht,
wenn hier ein Datum steht, addiere 7 Tage dazu und vergleiche das „ NEUEDATUM „ mit den Datum HEUTE. Ist das NEUEDATUM gleich oder grösser als das Datum HEUTE zeige die Werte der Zellen in den jeweiligen Textboxen an !
Hier die Kurzform:
Ich möchte alle Mitglieder ( Daten ) angezeigt bekommen die 7 Tage nach Brief2 noch nicht bei Zahlung erfolgt ein Datum stehen haben.
Mein Makro bisher lautet :

Private Sub CommandButton1_Click()
Dim a As Date
a = Format(Date, "dd,mm,yyyy")
Set finden = ActiveSheet.Range("e2:e25 ")
For Each Zelle In finden
If Zelle.Value + 7 < a Then
Zelle.Select
Exit For
End If
Next
TextBox1 = ActiveCell.Offset(0, -3) 'name
TextBox2 = ActiveCell.Offset(0, -4) 'datum
TextBox3 = ActiveCell.Offset(0, -2) 'betrag
TextBox4 = ActiveCell.Offset(0, -1) 'brief1
TextBox5 = ActiveCell               '.offset(0,1)    'brief2
End Sub

Hoffe doch – es hat jemand die Zeit – Muße und Lust sich meines Problems anzunehmen. Vielen Dank vorab
Grüsse
Reiner

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: matthias
Geschrieben am: 23.09.2015 10:21:29
Hallo Reiner,
wenn du wirklich alle Zeilen aufgelistet haben möchtest, musst du die Werte an eine Listbox mittels AddItem übergeben. So wie dein Code jetzt ausschaut, wird dir immer nur die erste Zelle angegeben, denn du verlässt deine Suchschleife nach dem ersten Fund.
Lege dir dazu eine 6-spaltige Listbox an mit folgendem Code:

Sub CommandButton1_Click()
Dim rZelle As Range
ListBox1.Clear
With ListBox1
    .AddItem "Name"
    .List(.ListCount - 1, 1) = "Datum"
    .List(.ListCount - 1, 2) = "Betrag"
    .List(.ListCount - 1, 3) = "Brief1"
    .List(.ListCount - 1, 4) = "Brief2"
    .List(.ListCount - 1, 5) = "Fundort"
End With
For Each rZelle In Columns("F").SpecialCells(xlCellTypeBlanks) 'alle leeren Zellen in Spalte F
    If IsDate(rZelle.Offset(0, -1).Value) Then ' Spalte E ist Datum?
        If rZelle.Offset(0, -1).Value + 7 >= Date Then
            With ListBox1
                .AddItem rZelle.Offset(0, -4) 'name
                .List(.ListCount - 1, 1) = rZelle.Offset(0, -5) 'datum
                .List(.ListCount - 1, 2) = rZelle.Offset(0, -3) 'betrag
                .List(.ListCount - 1, 3) = rZelle.Offset(0, -2) 'brief1
                .List(.ListCount - 1, 4) = rZelle.Offset(0, -1) 'brief2
                .List(.ListCount - 1, 5) = rZelle.Address       'Adresse
            End With
        End If
    End If
Next rZelle
End Sub

Abrufen kannst du die Daten dann mit ListBox1.List(Zeilenindex, Spalteindex), wobei beide Indexe bei Null beginnen. Um alle Einträge im Makro weiterzuverarbeiten, kann man mit einer For-Schleife alle Einträge durchgehen. Zeilenindex 0 enthält hierbei die Überschriften, welche nicht berücksichtig werden müssen.
For x = 1 To ListBox1.ListCount
lg Matthias

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: matthias
Geschrieben am: 23.09.2015 10:23:06
Ach, statt x sollte es Zeilenindex lauten.

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: reiner
Geschrieben am: 24.09.2015 08:37:06
Hallo Matthias,
erstmal vielen Dank für deine Hilfe !
Ich habe den code etwas umgestrickt und jetzt funtioniert er in soweit, daß mir die Daten in meiner UserForm in den entsprechenden Textboxen angezeigt werden !
Anscheinend hab ich am Anfang vergessen zu erwähnen - daß jeder Fall "einzeln" in den Textboxen erscheinen soll. Leider wird nur der erste Fall / Fund angezeigt ! Wie kann ich - wenn ich den CommandButton wieder betätige, den nächsten Fall/Fund in die Textboxen einlesen ?
Hier mein aktueller Code :

Sub CommandButton1_Click()
Dim a As Date
Dim rzelle As Range
Dim zelle As Range
a = Format(Date, "dd,mm,yyyy")
    
For Each rzelle In Columns("F").SpecialCells(xlCellTypeBlanks) 'alle leeren Zellen in Spalte F
    If IsDate(rzelle.Offset(0, -1).Value) Then ' Spalte E ist Datum?
        rzelle.Offset(0, -1).Select
        
        If ActiveCell.Value + 30 >= Date Then  ' hier ist die Differenz auf 30 tage erhöht !
        
        TextBox1 = ActiveCell.Offset(0, -3) 'name
        TextBox2 = ActiveCell.Offset(0, -4) 'datum
        TextBox3 = ActiveCell.Offset(0, -2) 'betrag
        TextBox4 = ActiveCell.Offset(0, -1) 'brief1
        TextBox5 = ActiveCell               '.offset(0,1)    'brief2
        
            
        End If
        Exit For
    End If
Next rzelle
'Hier müßte die Suche weiterlaufen - aber wie ???
End Sub

Vielen Dank für Deine / Eure Mühe vorab
Grüsse
Reiner

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: matthias
Geschrieben am: 24.09.2015 12:02:14
Hallo Reiner,
na dann machen wir es einfach anders. Statt mit einer For-Schleife nutzen wir die Suchfunktion. Der Button "Weitersuchen" ist dir sicher bekannt, der lässt sich in VBA mit dem "After"-Parameter nachahmen. Wir suchen dabei nach der aktiven Zelle.

'Daten suchen und an TextBox übergeben
Private Sub CommandButton1_Click()
Dim rZelle As Range
Set rZelle = Columns("F").Find(What:="", After:=Cells(ActiveCell.Row, 6)) 'Suche leere Zelle in  _
Spalte F
If rZelle Is Nothing Then
    MsgBox "Nichts gefunden, alles i.O.", , "Hinweis"
Else
    If rZelle.Offset(0, -5) = "" Then 'Ende der Tabelle erreicht (Spalte A leer)
        Cells(1, 5).Select
        Call UserForm_Initialize
        MsgBox "Nichts gefunden, oder Ende der Tabelle erreicht.", , "Hinweis"
        Set rZelle = Columns("F").FindNext(After:=Cells(ActiveCell.Row, 6))
    End If
    
    If IsDate(rZelle.Offset(0, -1).Value) Then ' Spalte E ist Datum?
        rZelle.Offset(0, -1).Select
        With ActiveCell
            If .Value + 30 >= Date Then             'hier ist die Differenz auf 30 tage erhöht ! _
                TextBox1 = .Offset(0, -3)           'name
                TextBox2 = .Offset(0, -4)           'datum
                TextBox3 = Format(.Offset(0, -2), "0.00 €") 'betragA
                TextBox4 = .Offset(0, -1)           'brief1
                TextBox5 = .Value                   'brief2
                TextBox6 = .Offset(0, 1)            'zahlung erfolgt
            End If
        End With
    End If
End If
End Sub
'geänderte Daten übernehmen
Private Sub CommandButton2_Click()
With ActiveCell
            If .Value + 30 >= Date Then             'hier ist die Differenz auf 30 tage erhöht ! _
                .Offset(0, -3) = TextBox1           'name
                .Offset(0, -4) = CDate(TextBox2)    'datum
                .Offset(0, -2) = CCur(TextBox3)     'betragA
                .Offset(0, -1) = CDate(TextBox4)    'brief1
                .Value = CDate(TextBox5)            'brief2
                .Offset(0, 1) = TextBox6            'zahlung erfolgt
            End If
        End With
End Sub
'Einstellungen beim Öffnen der Userform
Private Sub UserForm_Initialize()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
End Sub

Vorsicht beim Suchen: Die Zelle, welche im After-Parameter angegeben wird, muss im Suchbereich liegen. Die Lösung dazu siehst du ja.

Was mich ehrlich gesagt stört ist das "ActiveCell". Ich arbeite ungern damit zws. Zellen hin- und herzuspringen und sie zu markieren, denn das ist ein unnötiger Schritt. Da du aber darauf bestehst, bin ich dabei geblieben.
lg Matthias

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: reiner
Geschrieben am: 25.09.2015 08:50:29
Hallo Matthias,
langsam seh ich wohl den Wald vor lauter Bäumen nicht mehr - bzw ich steh aufm Schlauch !
Ich hab hier mal meine Datei hochgeladen
https://www.herber.de/bbs/user/100389.xlsm
schau sie Dir doch bitte einmal bei Gelegenheit an.
Dankeeee
Grüsse
Reiner

Bild

Betrifft: AW: Wenn Zelle leer - Datum prüfen und vergleichen
von: matthias
Geschrieben am: 25.09.2015 16:10:32
Hallo Reiner,
stimmt, da fehlt auch was. Gestern hat es meiner Meinung nach funktioniert gehabt oder ich habe mich ungenügender Beispiele bedient.

'Daten suchen und an TextBox übergeben
Private Sub CommandButton1_Click()
Dim rZelle As Range
Set rZelle = Columns("F").Find(What:="", After:=Cells(ActiveCell.Row, 6)) 'Suche leere Zelle in  _
Spalte F
If rZelle Is Nothing Then
    MsgBox "Nichts gefunden, alles i.O.", , "Hinweis"
Else
    rZelle.Offset(0, -1).Select
    
    If rZelle.Offset(0, -5) = "" Then 'Ende der Tabelle erreicht (Spalte A leer)
        Cells(1, 5).Select
        TextBox1 = ""
        TextBox2 = ""
        TextBox3 = ""
        TextBox4 = ""
        TextBox5 = ""
        'TextBox6 = ""
        MsgBox "Nichts gefunden, oder Ende der Tabelle erreicht.", , "Hinweis"
        Exit Sub
    End If
    
    If IsDate(rZelle.Offset(0, -1).Value) Then ' Spalte E ist Datum
        With ActiveCell
            If .Value + 30 >= Date Then
                TextBox1 = .Offset(0, -3)                   'name
                TextBox2 = .Offset(0, -4)                   'datum
                TextBox3 = Format(.Offset(0, -2), "0.00 €") 'betragA
                TextBox4 = .Offset(0, -1)                   'brief1
                TextBox5 = .Value                           'brief2
                'TextBox6 = .Offset(0, 1)         'zahlung erfolgt
            Else: Call CommandButton1_Click
            End If
        End With
    Else: Call CommandButton1_Click
    End If
End If
End Sub
Die Suche erfolgt immer ab der aktuell markierten Zelle, wenn du also die komplette Tabelle durchsuchen willst, solltest du die oberste Zelle markieren. Oder du drückst nach der Mitteilung "Ende Erreicht" einfach nochmal auf den Button und er fängt wieder von vorn an.
lg Matthias

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Wenn Zelle leer - Datum prüfen und vergleichen"