AW: Zwei Spalten miteinander vergleichen
11.01.2019 08:42:35
Zna
Weil das meine aufgabenstellung ist, ich habe jetzt auch schon ein paar zeilen dazu geschrieben, aber es werden nicht alle ergebnisse gefunden...
das ist mein code
ub SAP_DNF(WE_Name)
Dim SlaveOVV As String
Dim OVV_Counter, Shifter As Integer
On Error Resume Next
'1
For Each Projekt In Projektliste
Select Case WE_Name
Case Is = Projekt
Select Case Projekt.Offset(0, 65) 'DNF
Case Is ""
Workbooks("Datenbank.xlsb").Activate
Call VT_Master.Blockdefinition_PAN
Call VT_Master.Blockdefinition_POS
Call VT_Master.Blockdefinition_SWG
Workbooks.Open Projekt.Offset(0, 65)
SlaveOVV = ActiveWorkbook.Name
Sheets(1).Select
'### Unterscheidung EINZELFELDER vs. BLOCKANLAGEN VERALTET
' Dim BLOCK_FELD As Integer
' BLOCK_FELD = InStr(1, Cells(3, 3), ";", vbTextCompare)
' Select Case BLOCK_FELD
' Case 0
'#### Formatieren des Blattes, damit die Zuordnung funktioniert _
EINZELFELDER
Call VT_Master_SAP.SAP_Blockformat
For OVV_Counter = 2 To ActiveSheet.UsedRange.Rows. _
Count
Select Case Len(Cells(OVV_Counter, 2))
Case 2
Cells(OVV_Counter, 2) = "'0" & Cells( _
OVV_Counter, 2)
Case Else
End Select
Select Case Len(Cells(OVV_Counter, 5))
Case 2
Cells(OVV_Counter, 5) = Cells( _
OVV_Counter, 5) & "." & Cells(OVV_Counter, 6)
Case Else
End Select
Select Case Cells(OVV_Counter, 3).Value
Case ""
Select Case Left(Cells(OVV_Counter, _
4).Value, 1) ' Shifter unterscheidet, ob der Wert mit "'" anfängt oder nicht
Case Is = "'"
Shifter = 2
Case Else
Shifter = 1
End Select
Cells(OVV_Counter, 3) = Right(Cells( _
OVV_Counter, 4), Len(Cells(OVV_Counter, 4)) - Shifter)
Case Else
Select Case Left(Cells(OVV_Counter, _
4).Value, 1)
Case Is = "'"
Shifter = 2
Case Else
Shifter = 1
End Select
Cells(OVV_Counter, 3) = Right(Cells( _
OVV_Counter, 3), Len(Cells(OVV_Counter, 3)) - Shifter)
End Select
'2
Next
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