1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
| Sub DeleteWarning()
Dim DerLig As Long, l As Long, i As Long, k As Long, ib As Long, kb As Long, ih As Long, kh As Long, ip As Long, kp As Long, LastRowb As Long, LastRowh As Long, LastRowp As Long, h As Long, b As Long, p As Long
Dim CL As Range
' Delete all rows where there are a warning which not stop the turbine
With Sheets(1)
DerLig = .Range("A65000").End(xlUp).Row
For l = DerLig To 3 Step -1
If .Cells(l, 11).Value = 0 Then
If CL Is Nothing Then
Set CL = .Cells(l, 11)
Else
Set CL = Union(.Cells(l, 11), CL)
End If
End If
Next l
Set CL = CL.EntireRow
CL.Delete
End With
'Put data in the correst sheet and in the correct turnines column
DerLig = Sheets("ExportedData").Range("A65000").End(xlUp).Row
For i = DerLig To 3 Step -1
For k = 1 To 6
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 3).Value Then Sheets("Bruck").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 3).Value Then Sheets("Bruck").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 3).Value Then Sheets("Bruck").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 3).Value Then Sheets("Bruck").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 3).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 3).Value Then Sheets("Bruck").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 4).Value Then Sheets("Hollerm").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 4).Value Then Sheets("Hollerm").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 4).Value Then Sheets("Hollerm").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 4).Value Then Sheets("Hollerm").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 4).Value Then Sheets("Hollerm").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(7, 4).Value Then Sheets("Hollerm").Cells(i, k + 35).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(8, 4).Value Then Sheets("Hollerm").Cells(i, k + 42).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(9, 4).Value Then Sheets("Hollerm").Cells(i, k + 49).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 4).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(10, 4).Value Then Sheets("Hollerm").Cells(i, k + 56).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(2, 5).Value Then Sheets("Petronell").Cells(i, k).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(3, 5).Value Then Sheets("Petronell").Cells(i, k + 7).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(4, 5).Value Then Sheets("Petronell").Cells(i, k + 14).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(5, 5).Value Then Sheets("Petronell").Cells(i, k + 21).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(6, 5).Value Then Sheets("Petronell").Cells(i, k + 28).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(7, 5).Value Then Sheets("Petronell").Cells(i, k + 35).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(8, 5).Value Then Sheets("Petronell").Cells(i, k + 42).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(9, 5).Value Then Sheets("Petronell").Cells(i, k + 49).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(10, 5).Value Then Sheets("Petronell").Cells(i, k + 56).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(11, 5).Value Then Sheets("Petronell").Cells(i, k + 63).Value = Sheets("ExportedData").Cells(i, k).Value
If Sheets("ExportedData").Cells(i, 8).Value = Sheets("WarningAndError").Cells(1, 5).Value And Sheets("ExportedData").Cells(i, 7).Value = Sheets("WarningAndError").Cells(12, 5).Value Then Sheets("Petronell").Cells(i, k + 70).Value = Sheets("ExportedData").Cells(i, k).Value
Next k
Next i
'Delete all cells where there are nothing inside
With Sheets("Bruck")
For b = 1 To 35 Step 7
LastRowb = Sheets("bruck").Cells(65000, b).End(xlUp).Row
For ib = LastRowb To 3 Step -1
For kb = 1 To 35
If .Cells(ib, kb).Value = "" Then .Cells(ib, kb).Delete xlShiftUp
Next kb
Next ib
Next b
End With
With Sheets("petronell")
For p = 1 To 63 Step 7
LastRowp = Sheets("petronell").Cells(65000, p).End(xlUp).Row
For ip = LastRowp To 3 Step -1
For kp = 1 To 77
If .Cells(ip, kp).Value = "" Then .Cells(ip, kp).Delete xlShiftUp
Next kp
Next ip
Next p
End With
With Sheets("Hollerm")
For h = 1 To 63 Step 7
LastRowh = Sheets("Hollerm").Cells(65000, h).End(xlUp).Row
For ih = LastRowh To 3 Step -1
For kh = 1 To 63
If .Cells(ih, kh).Value = "" Then .Cells(ih, kh).Delete xlShiftUp
Next kh
Next ih
Next h
End With
End Sub |
Partager