|
Hallo Forum,
ich habe folgendes Problem:
In einem Worksheet stehen folgende Daten (natürlich nur erdacht aus Datenschutz-Gründen):
Abflugflughafen, Zielflughafen, Flugzeug-Registrierung, Abflugzeit, Ankunftszeit
|
DEP |
ARR |
REG |
ETD |
ETA |
|
LEJ |
LTN |
A |
30.6.16 2:10 |
30.6.16 3:52 |
|
LTN |
MXP |
A |
30.6.16 8:00 |
30.6.16 9:54 |
|
MXP |
LHR |
A |
30.6.16 18:00 |
30.6.16 20:02 |
|
LHR |
LEJ |
A |
30.6.16 21:50 |
30.6.16 23:38 |
|
LEJ |
EMA |
A |
2.7.16 0:30 |
2.7.16 2:20 |
|
EMA |
BFS |
B |
2.7.16 3:55 |
2.7.16 4:55 |
|
BFS |
EMA |
B |
4.7.16 19:25 |
4.7.16 20:30 |
|
EMA |
BRU |
B |
4.7.16 22:00 |
4.7.16 23:10 |
|
VIT |
SVQ |
C |
30.6.16 5:41 |
30.6.16 6:54 |
|
SVQ |
VIT |
D |
30.6.16 18:16 |
30.6.16 19:28 |
|
VIT |
LEJ |
D |
30.6.16 21:07 |
30.6.16 23:22 |
|
LEJ |
HEL |
D |
1.7.16 2:18 |
1.7.16 4:10 |
|
HEL |
LEJ |
E |
1.7.16 19:40 |
1.7.16 21:55 |
|
LEJ |
CDG |
E |
3.7.16 18:45 |
3.7.16 20:25 |
|
CDG |
LEJ |
E |
4.7.16 21:15 |
4.7.16 22:50 |
|
LEJ |
BRU |
F |
3.7.16 18:35 |
3.7.16 19:55 |
|
BRU |
HEL |
F |
3.7.16 21:30 |
4.7.16 0:05 |
|
HEL |
BRU |
F |
4.7.16 1:35 |
4.7.16 4:25 |
|
BRU |
LEJ |
F |
4.7.16 22:10 |
4.7.16 23:24
|
In ein anderes Sheet soll eingetragen werden, wo sich das Flugzeug gerade befindet. Dazu wird mit der geweiligen Zeit ("Now") die Spalte in mehreren For-Schleifen abgelaufen und die Daten (Plural Datum) abgeglichen, ob sie kleiner oder größer als Now sind. Soweit so gut, nur leider liefert mir mein Code immer nur die letzte Spalte der jeweiligen Flugzeug-Registrierung und bezieht sich auf diese. Hier mein Code:
Sub locationupdate()
Dim endflight As Integer
Dim enda300fleet As Integer
Dim endb757fleet As Integer
Dim endbus As Integer
Dim endboe As Integer
Dim x As Long
Dim y As Long
Dim z As Long
Dim flyreg As Long
Dim ACReg As String
ThisWorkbook.Worksheets("Allocation").Cells(2, 11) = Now
ThisWorkbook.Worksheets("Allocation").Cells(2, 12) = Date
ThisWorkbook.Worksheets("Allocation").Cells(2, 13) = Time
ControlPanel.Hide
UserForm2.Show
UserForm2.Caption = "Locations werden aktualisiert... "
DoEvents
'
endflight = ThisWorkbook.Worksheets("Allocation").Cells(Rows.Count, 7).End(xlUp).Row
endbus = ThisWorkbook.Worksheets("AIRBUS").Cells(Rows.Count, 3).End(xlUp).Row
endboe = ThisWorkbook.Worksheets("BOEING").Cells(Rows.Count, 3).End(xlUp).Row
enda300fleet = ThisWorkbook.Worksheets("ABFleet").Cells(Rows.Count, 1).End(xlUp).Row
endb757fleet = ThisWorkbook.Worksheets("BOEFleet").Cells(Rows.Count, 1).End(xlUp).Row
endflight = ThisWorkbook.Worksheets("Allocation").Cells(Rows.Count, 7).End(xlUp).Row
For y = 6 To enda300fleet
ACReg = ThisWorkbook.Worksheets("ABFleet").Cells(y, 1)
z = 6
For z = 6 To endbus
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 3) = ACReg Then
UserForm2.Caption = "Locations werden aktualisiert... " & ACReg
DoEvents
'hier der wichtige Teil wo es wohl einen Denkfehler meinerseits gibt:
For flyreg = 7 To endflight
If ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 10).Value < Now < ThisWorkbook.Worksheets("Allocation").Cells(flyreg + 1, 9).Value And ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 7) = ACReg And ThisWorkbook.Worksheets("Allocation").Cells(flyreg + 1, 7) = ACReg Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) = ThisWorkbook.Worksheets("Allocation").Cells(flyreg + 1, 3)
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew" Or ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS" Then
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) <> "LEJ" Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS"
Else
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew"
End If
End If
ElseIf ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 9).Value < Now < ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 10).Value And ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 7) = ACReg Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) = ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 3)
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew" Or ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS" Then
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) <> "LEJ" Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS"
Else
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew"
End If
End If
ElseIf ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 10).Value < Now < ThisWorkbook.Worksheets("Allocation").Cells(flyreg + 1, 9).Value And ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 7) = ACReg And ThisWorkbook.Worksheets("Allocation").Cells(flyreg + 1, 7) <> ACReg Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) = ThisWorkbook.Worksheets("Allocation").Cells(flyreg, 4)
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew" Or ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS" Then
If ThisWorkbook.Worksheets("AIRBUS").Cells(z, 6) <> "LEJ" Then
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "OS"
Else
ThisWorkbook.Worksheets("AIRBUS").Cells(z, 5) = "Crew"
End If
End If
End If
Next flyreg
End If
Next z
Next y
UserForm2.Hide
Application.ScreenUpdating = True
End Sub
Hat jemand eine Idee?
|