AW: Hier mein Versuch das Prob zu lösen
15.08.2020 11:50:46
Curly
Hallo,
ich konnte mir einen Code erstellen, der zumindest das gewünscht macht, eventuell hat noch jemand verbesserungsvorschläge und evtl auch Performance Optimierung.
Sub SV()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Dim i, x, intLastRowTab1, intLastRowSV, AnzSV As Integer
Dim NDL As String
NDL = Mid(ThisWorkbook.Name, 10, 3)
intLastRowTab1 = Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
intLastRowSV = Sheets(NDL & "_SV").Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To intLastRowTab1
AnzSV = WorksheetFunction.CountIfs(Range(NDL & "_SV!A:A"), Sheets("Tabelle1").Cells(i, 1).Value, _
Range(NDL & "_SV!H:H"), "=" & CDbl(Date)) '"=COUNTIFS(C,""C0527988"",C[7],""""&TODAY())"
If AnzSV > 0 Then
Sheets("Tabelle1").Cells(i, 8).Value = "Ja (" & AnzSV & ")"
For x = 4 To intLastRowSV
If Sheets("Tabelle1").Cells(i, 1).Value = Sheets(NDL & "_SV").Cells(x, 1) Then
If (Sheets(NDL & "_SV").Cells(x, 9).Value > CDbl(Date)) And (Sheets("Tabelle1").Cells(i, _
9).Value = "" Or Sheets("Tabelle1").Cells(i, 9).Value > Sheets(NDL & "_SV").Cells(x, 9).Value) Then
Sheets("Tabelle1").Cells(i, 9).Value = Sheets(NDL & "_SV").Cells(x, 9).Value
End If
End If
Next x
Else
Sheets("Tabelle1").Cells(i, 8).Value = "Nein"
End If
Next i
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
Gruß Curly