wie fasse ich diesen code am besten zusammen
11.01.2019 10:59:15
Zna
ich habe mal eine frage, wie kann ich denn diesen code etwas zsuammenfassen, kann ich die for each schleifen zusammenfassen oder die if bedingungen ?
Vielen dank
Set OVV_Range = Sheets(1).Range(Cells(2, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
Dim MustDNF As Integer
Dim CheckDNF As Integer
CheckDNF = 0
For MustDNF = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(MustDNF, 1) = "" Then
Exit For
Else
End If
Next
MustDNF = MustDNF - 2
'Lauf NULL - Identität
For Each OVV In OVV_Range
For Each POS In POS_Range
Select Case POS.Offset(0, 8)
Case Is = OVV
Select Case POS.Offset(0, 13)
Case OVV.Offset(0, 2)
POS.Offset(0, 26) = "DNF"
OVV.Offset(0, 8) = "OK"
CheckDNF = CheckDNF + 1
Case Else
End Select
Case Else
End Select
Next
Next
'Lauf eins - Leistungsschalter
For Each OVV In OVV_Range
For Each POS In POS_Range
Select Case POS.Offset(0, 8)
Case Is = OVV
Select Case OVV.Offset(0, 8)
Case Is "OK"
Select Case InStr(1, POS.Offset(0, 13), OVV.Offset(0, 2), vbTextCompare)
Case Is 0
POS.Offset(0, 26) = "DNF"
OVV.Offset(0, 8) = "OK"
CheckDNF = CheckDNF + 1
Case Else
End Select
Case Else
End Select
Case Else
End Select
Next
Next
' Zweiter Lauf, wenn nicht alle gefunden wurden
If MustDNF CheckDNF Then
For Each OVV In OVV_Range
For Each POS In POS_Range
Select Case POS.Offset(0, 8)
Case Is = OVV
Select Case OVV.Offset(0, 8)
Case Is "OK"
Select Case InStr(1, Left(POS.Offset(0, 13), 10), Left(OVV.Offset(0, 2), 10), vbTextCompare)
Case Is 0
POS.Offset(0, 26) = "DNF"
OVV.Offset(0, 8) = "OK"
CheckDNF = CheckDNF + 1
Case Else
End Select
Case Else
End Select
Case Else
End Select
Next
Next
Else
End If
'
' Case Else
' End Select
Case Else
End Select
Case Else
End Select
Workbooks(SlaveOVV).Activate
ActiveWorkbook.Saved = True
Application.DisplayAlerts = False
Workbooks(SlaveOVV).Close
Application.DisplayAlerts = True
Next
Workbooks("Datenbank.xlsb").Activate
If CheckDNF MustDNF Then
MsgBox "Es wurden nur " & CheckDNF & " von " & MustDNF & " Devices not found zugeordnet. Bitte händisch prüfen"
Else
MsgBox "Es wurden alle " & CheckDNF & " von " & MustDNF & " Devices not found zugeordnet."
End If
End Sub