ich habe anbei eine Datei hochgeladen.
Problem ich möchte nach einem Datum und dem dazugehörigen Projekt suchen.
Wenn es vorhanden ist dann soll eine Ausgabe statt finden.
Danke!
Gruß
Chrissi
https://www.herber.de/bbs/user/85523.xlsm
Option Explicit
Private Sub UserForm_Initialize()
Dim ii As Long
Dim vDat As Date ' warum war das Variant?
Dim sTxt As String
For ii = 1 To 2
Controls("ComboBox" & ii).Clear
Next ii
With ThisWorkbook.Worksheets("Projekt")
.Activate
For ii = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
sTxt = .Cells(ii, 1) & " " & .Cells(ii, 2) & " " & .Cells(ii, 3) & " " & .Cells(ii, 4)
ComboBox2.AddItem sTxt
Next ii
End With
vDat = Date
vDat = vDat - 5
For ii = 1 To 40
vDat = vDat + 1
ComboBox1.AddItem vDat
Next ii
End Sub
Private Sub ComboBox2_Change()
Dim DatSuche As Date
Dim xSuche As String
Dim ii As Long, Endrow As Long
Dim Mldg As String
DatSuche = ComboBox1
xSuche = ComboBox2
With ThisWorkbook.Worksheets("Tagesjournal")
Endrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For ii = 5 To Endrow
If .Cells(ii, 1) = DatSuche And .Cells(ii, 2) = xSuche Then
lblZnr.Caption = .Cells(Endrow, 7)
Exit For
End If
Next ii
If ii > Endrow Then
Beep
Mldg = MsgBox("Tagesbericht nicht vorhanden...", _
vbYes + vbQuestion, "Fehlermeldung Projekt ...", "", 16)
ComboBox2.SetFocus
lblZnr.Caption = ""
GoTo Ende
End If
End With
Ende:
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Gefunden werden kann bei den vorliegenden Daten nur der Musterbau Musterweg... am 25.05., sonst nichts.
Option Explicit
Private Sub UserForm_Initialize()
Dim ii As Long
Dim vDat As Date ' warum war das Variant?
Dim sTxt As String
For ii = 1 To 2
Controls("ComboBox" & ii).Clear
Next ii
With ThisWorkbook.Worksheets("Projekt")
.Activate
For ii = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
sTxt = .Cells(ii, 1) & " " & .Cells(ii, 2) & " " & .Cells(ii, 3) & " " & .Cells(ii, 4)
ComboBox2.AddItem sTxt
Next ii
End With
vDat = Date - 5
For ii = 1 To 40
vDat = vDat + 1
ComboBox1.AddItem vDat
Next ii
End Sub
Private Sub ComboBox2_Change()
Dim DatSuche As Date
Dim xSuche As String
Dim ii As Long, Endrow As Long
Dim Mldg As String
DatSuche = ComboBox1
xSuche = ComboBox2
With ThisWorkbook.Worksheets("Tagesjournal")
Endrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For ii = 3 To Endrow
If .Cells(ii, 1) = DatSuche And .Cells(ii, 2) = xSuche Then
lblZnr.Caption = .Cells(ii, 7)
Exit For
End If
Next ii
If ii > Endrow Then
Beep
Mldg = MsgBox("Tagesbericht nicht vorhanden...", _
vbYes + vbQuestion, "Fehlermeldung Projekt ...", "", 16)
ComboBox2.SetFocus
lblZnr.Caption = ""
GoTo Ende
End If
End With
Ende:
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich und: schönes Rest-Wochenende!