Sub Correction_terms_of_business_trips()
For i = 2 To 9
Worksheets("Ñïèñîê öåëåé").Activate
Dim goal As String
Dim term As Integer
goal = Cells(i, 1)
term = Cells(i, 2)
Worksheets("Êîìàíäèðîâêè").Activate
For j = 2 To 298
If Cells(j, 4) = goal Then
If Cells(j, 3) > term Then
Cells(j, 3) = term
End If
End If
Next j
Next i
End Sub
Sub Correction_of_employees()
Dim i As Integer
i = 3
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) <> ""
Dim j As Integer
j = i - 1
Do While j > 2 And Cells(j, 2) <> Cells(i, 2)
j = j - 1
Loop
If Cells(j, 2) = Cells(i, 2) Then
Dim business_trip_first As Long
Dim business_trip_second As Long
business_trip_first = Cells(j, 1)
business_trip_second = Cells(i, 1)
Worksheets("Êîìàíäèðîâêè").Activate
Dim date_of_business_trip_first As Date
Dim date_of_business_trip_second As Date
For k = 2 To 298
If Cells(k, 1) = business_trip_first Then
date_of_business_trip_first = DateAdd("d", Cells(k, 3), Cells(k, 2))
End If
If Cells(k, 1) = business_trip_second Then
date_of_business_trip_second = Cells(k, 2)
End If
Next k
If date_of_business_trip_second <= date_of_business_trip_first Then
Worksheets("Êòî åäåò").Activate
Rows(i).Delete
i = i - 1
End If
End If
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
End Sub
Public Function Have_an_interpreter(ByRef i, business_trip) As Boolean
Dim is_interpreter As Boolean
is_interpreter = False
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) = business_trip
Dim employee As String
employee = Cells(i, 2)
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
Dim profession As String
For j = 2 To 76
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
For j = 2 To 69
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
If profession = "ïåðåâîä÷èê" Or profession = "ñåêðåòàðü-ïåðåâîä÷èê" Or profession = "àññèñòåíò ïåðåâîä÷èêà" Then
is_interpreter = True
Exit Do
End If
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
Have_an_interpreter = is_interpreter
End Function
Public Function Free_interpreter(new_business_trip) As String
Dim employee As String
Dim j As Integer
Dim business_trip As Long
Dim ok As Boolean
Dim term_begin As Date
Dim term_end As Date
Dim new_term_begin As Date
Dim new_term_end As Date
For i = 2 To 76
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
If Cells(i, 4) = "ïåðåâîä÷èê" Or Cells(i, 4) = "ñåêðåòàðü-ïåðåâîä÷èê" Or Cells(i, 4) = "àññèñòåíò ïåðåâîä÷èêà" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_interpreter = employee
Exit Function
End If
End If
Next i
For i = 2 To 69
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
If Cells(i, 4) = "ïåðåâîä÷èê" Or Cells(i, 4) = "ñåêðåòàðü-ïåðåâîä÷èê" Or Cells(i, 4) = "àññèñòåíò ïåðåâîä÷èêà" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_interpreter = employee
Exit Function
End If
End If
Next i
End Function
Public Function Have_an_engineer(ByRef i, business_trip) As Boolean
Dim is_engineer As Boolean
is_engineer = False
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) = business_trip
Dim employee As String
employee = Cells(i, 2)
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
Dim profession As String
For j = 2 To 76
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
For j = 2 To 69
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
If profession = "èíæåíåð" Or profession = "àññèñòåíò èíæåíåðà" Or profession = "ãëàâíûé èíæåíåð" Then
is_engineer = True
Exit Do
End If
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
Have_an_engineer = is_engineer
End Function
Public Function Free_engineer(new_business_trip) As String
Dim employee As String
Dim j As Integer
Dim business_trip As Long
Dim ok As Boolean
Dim term_begin As Date
Dim term_end As Date
Dim new_term_begin As Date
Dim new_term_end As Date
For i = 2 To 76
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
If Cells(i, 4) = "èíæåíåð" Or Cells(i, 4) = "àññèñòåíò èíæåíåðà" Or Cells(i, 4) = "ãëàâíûé èíæåíåð" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_engineer = employee
Exit Function
End If
End If
Next i
For i = 2 To 69
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
If Cells(i, 4) = "èíæåíåð" Or Cells(i, 4) = "àññèñòåíò èíæåíåðà" Or Cells(i, 4) = "ãëàâíûé èíæåíåð" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_engineer = employee
Exit Function
End If
End If
Next i
End Function
Public Function Have_an_technologist_or_builder(ByRef i, business_trip) As Boolean
Dim is_technologist_or_builder As Boolean
is_technologist_or_builder = False
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) = business_trip
Dim employee As String
employee = Cells(i, 2)
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
Dim profession As String
For j = 2 To 76
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
For j = 2 To 69
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
If profession = "òåõíîëîã" Or profession = "ñòðîèòåëü" Then
is_technologist_or_builder = True
Exit Do
End If
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
Have_an_technologist_or_builder = is_technologist_or_builder
End Function
Public Function Free_technologist_or_builder(new_business_trip) As String
Dim employee As String
Dim j As Integer
Dim business_trip As Long
Dim ok As Boolean
Dim term_begin As Date
Dim term_end As Date
Dim new_term_begin As Date
Dim new_term_end As Date
For i = 2 To 76
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
If Cells(i, 4) = "òåõíîëîã" Or Cells(i, 4) = "ñòðîèòåëü" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_technologist_or_builder = employee
Exit Function
End If
End If
Next i
For i = 2 To 69
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
If Cells(i, 4) = "òåõíîëîã" Or Cells(i, 4) = "ñòðîèòåëü" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_technologist_or_builder = employee
Exit Function
End If
End If
Next i
End Function
Public Function Have_an_builder_or_sectione_chief_or_foreman(ByRef i, business_trip) As Boolean
Dim is_builder_or_sectione_chief_or_foreman As Boolean
is_builder_or_sectione_chief_or_foreman = False
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) = business_trip
Dim employee As String
employee = Cells(i, 2)
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
Dim profession As String
For j = 2 To 76
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
For j = 2 To 69
If Cells(j, 1) = employee Then
profession = Cells(j, 4)
Exit For
End If
Next j
If profession = "ñòðîèòåëü" Or profession = "íà÷àëüíèê ó÷àñòêà" Or profession = "ïðîðàá" Then
is_builder_or_sectione_chief_or_foreman = True
Exit Do
End If
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
Have_an_builder_or_sectione_chief_or_foreman = is_builder_or_sectione_chief_or_foreman
End Function
Public Function Free_builder_or_sectione_chief_or_foreman(new_business_trip) As String
Dim employee As String
Dim j As Integer
Dim business_trip As Long
Dim ok As Boolean
Dim term_begin As Date
Dim term_end As Date
Dim new_term_begin As Date
Dim new_term_end As Date
For i = 2 To 76
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
If Cells(i, 4) = "ñòðîèòåëü" Or Cells(i, 4) = "íà÷àëüíèê ó÷àñòêà" Or Cells(i, 4) = "ïðîðàá" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_builder_or_sectione_chief_or_foreman = employee
Exit Function
End If
End If
Next i
For i = 2 To 69
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
If Cells(i, 4) = "ñòðîèòåëü" Or Cells(i, 4) = "íà÷àëüíèê ó÷àñòêà" Or Cells(i, 4) = "ïðîðàá" Then
employee = Cells(i, 1)
j = 2
ok = True
Worksheets("Êòî åäåò").Activate
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Êîìàíäèðîâêè").Activate
term_begin = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 2, 0)
term_end = DateAdd("d", term_begin, Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0))
new_term_begin = Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 2, 0)
new_term_end = DateAdd("d", new_term_begin, Application.WorksheetFunction.VLookup(new_business_trip, Range("A2:C298"), 3, 0))
If (term_end >= new_term_begin And term_begin <= new_term_begin) Or (term_end >= new_term_end And term_begin <= new_term_end) Then
ok = False
Exit Do
End If
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
If ok Then
Free_builder_or_sectione_chief_or_foreman = employee
Exit Function
End If
End If
Next i
End Function
Sub Analysis_of_employees()
Dim i As Integer
i = 2
Dim id_business_trip As Integer
Dim business_trip As Long
Dim employee As String
id_business_trip = 1
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) <> ""
business_trip = Cells(i, 1)
If business_trip <> Cells(i - 1, 1) Then
id_business_trip = id_business_trip + 1
End If
Worksheets("Êîìàíäèðîâêè").Activate
Dim destination As String
destination = Cells(id_business_trip, 5)
For j = 1 To 27
Worksheets("Ñïèñîê ãîðîäîâ âíå").Activate
If destination = Cells(j, 1) Then
If Not Have_an_interpreter(i, business_trip) Then
employee = Free_interpreter(business_trip)
Worksheets("Êòî åäåò").Activate
Rows(i).Select
Selection.Insert Shift:=xlDown
Cells(i, 1) = business_trip
Cells(i, 2) = employee
Else
Worksheets("Êòî åäåò").Activate
Do While business_trip = Cells(i + 1, 1)
i = i + 1
Loop
End If
Exit For
End If
Next j
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
i = 2
id_business_trip = 1
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) <> ""
business_trip = Cells(i, 1)
If business_trip <> Cells(i - 1, 1) Then
id_business_trip = id_business_trip + 1
End If
Worksheets("Êîìàíäèðîâêè").Activate
Dim goal As String
If business_trip <> Cells(id_business_trip, 1) Then
id_business_trip = id_business_trip + 1
End If
goal = Cells(id_business_trip, 4)
If goal = "Èñïûòàíèÿ" Then
If Not Have_an_engineer(i, business_trip) Then
employee = Free_engineer(business_trip)
Worksheets("Êòî åäåò").Activate
Rows(i).Select
Selection.Insert Shift:=xlDown
Cells(i, 1) = business_trip
Cells(i, 2) = employee
Else
Worksheets("Êòî åäåò").Activate
Do While business_trip = Cells(i + 1, 1)
i = i + 1
Loop
End If
End If
If goal = "Ïðåäâàðèòåëüíîå îáñëåäîâàíèå" Then
If Not Have_an_technologist_or_builder(i, business_trip) Then
employee = Free_technologist_or_builder(business_trip)
Worksheets("Êòî åäåò").Activate
Rows(i).Select
Selection.Insert Shift:=xlDown
Cells(i, 1) = business_trip
Cells(i, 2) = employee
Else
Worksheets("Êòî åäåò").Activate
Do While business_trip = Cells(i + 1, 1)
i = i + 1
Loop
End If
End If
If goal = "Ñòðîèòåëüñòâî" Then
If Not Have_an_builder_or_sectione_chief_or_foreman(i, business_trip) Then
employee = Free_builder_or_sectione_chief_or_foreman(business_trip)
Worksheets("Êòî åäåò").Activate
Rows(i).Select
Selection.Insert Shift:=xlDown
Cells(i, 1) = business_trip
Cells(i, 2) = employee
Else
Worksheets("Êòî åäåò").Activate
Do While business_trip = Cells(i + 1, 1)
i = i + 1
Loop
End If
End If
Do While business_trip = Cells(i + 1, 1)
i = i + 1
Loop
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
End Sub
Sub Plan_of_business_trip()
Sheets.Add After:=Sheets(Sheets.count)
Sheets(Sheets.count).Select
Sheets(Sheets.count).Name = "Ïëàí êîìàíäèðîâîê"
Dim enployee As String
Dim j As Integer
Dim business_trip As Long
Dim count As Integer
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
For i = 2 To 76
employee = Cells(i, 1)
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Cells(i - 1, 1) = employee
Worksheets("Êòî åäåò").Activate
count = 2
j = 2
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Cells(i - 1, count) = business_trip
count = count + 1
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
Worksheets("ÇÀÎ ""Ñòðîèòåëü""").Activate
Next i
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
For i = 2 To 69
employee = Cells(i, 1)
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Cells(i + 74, 1) = employee
Worksheets("Êòî åäåò").Activate
count = 2
j = 2
Do While Cells(j, 1) <> ""
If Cells(j, 2) = employee Then
business_trip = Cells(j, 1)
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Cells(i + 74, count) = business_trip
count = count + 1
End If
j = j + 1
Worksheets("Êòî åäåò").Activate
Loop
Worksheets("ÍÈÈ ""Ðàññâåò""").Activate
Next i
End Sub
Sub Lowly_involved()
Sheets.Add After:=Sheets(Sheets.count)
Sheets(Sheets.count).Select
Sheets(Sheets.count).Name = "Ìàëî çàäåéñòâîâàíû"
Dim count As Integer
Dim i As Integer
Dim employee As String
Dim id As Integer
id = 1
i = 1
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Do While Cells(i, 1) <> ""
count = 0
employee = Cells(i, 1)
Do While Cells(i, count + 2) <> ""
count = count + 1
Loop
If count <= 5 Then
Worksheets("Ìàëî çàäåéñòâîâàíû").Activate
Cells(id, 1) = employee
id = id + 1
End If
i = i + 1
Worksheets("Ïëàí êîìàíäèðîâîê").Activate
Loop
End Sub
Sub Cost_of_business_trip()
Sheets.Add After:=Sheets(Sheets.count)
Sheets(Sheets.count).Select
Sheets(Sheets.count).Name = "Ñòîèìîñòü êîìàíäèðîâîê"
Dim count As Integer
Dim i As Integer
Dim business_trip As Long
Dim destination As String
Dim term As Long
Dim cost As Long
Dim id As Integer
Dim real_cost As Long
id = 1
i = 2
count = 1
Worksheets("Êòî åäåò").Activate
Do While Cells(i, 1) <> ""
business_trip = Cells(i, 1)
count = 1
Do While Cells(i, 1) = Cells(i + 1, 1)
i = i + 1
count = count + 1
Loop
Worksheets("Êîìàíäèðîâêè").Activate
destination = Application.WorksheetFunction.VLookup(business_trip, Range("A2:E298"), 5, 0)
destination = Mid(destination, InStr(destination, "(") + 1, Len(destination) - InStr(destination, "(") - 1)
term = Application.WorksheetFunction.VLookup(business_trip, Range("A2:C298"), 3, 0)
Worksheets("Ñïèñîê îáëàñòåé").Activate
For j = 2 To 55
If destination = Cells(j, 1) Then
If Cells(j, 2) = "" Then
cost = 1500 * term * count
Else
cost = Cells(j, 2) * term * count
End If
cost = cost + Cells(j, 3) * count * 2
End If
Next j
Worksheets("Ñïèñîê ñòðàí").Activate
For j = 2 To 12
If destination = Cells(j, 1) Then
cost = Cells(j, 2) * term * count
cost = cost + Cells(j, 3) * count * 2
End If
Next j
Worksheets("Ñòîèìîñòü êîìàíäèðîâîê").Activate
Cells(id, 1) = business_trip
Cells(id, 2) = cost
id = id + 1
i = i + 1
Worksheets("Êòî åäåò").Activate
Loop
Worksheets("Êîìàíäèðîâêè").Activate
For j = 2 To 298
Cells(j, 8) = Cells(j, 6)
Next j
For j = 2 To 298
business_trip = Cells(j, 1)
cost = Cells(j, 8)
If cost <> 0 Then
Worksheets("Ñòîèìîñòü êîìàíäèðîâîê").Activate
For k = 1 To 295
If Cells(k, 1) = business_trip Then
Cells(k, 3) = cost - Cells(k, 2)
Exit For
End If
Next k
Else
Worksheets("Ñòîèìîñòü êîìàíäèðîâîê").Activate
For k = 1 To 295
If Cells(k, 1) = business_trip Then
Cells(k, 3) = Cells(k, 2)
real_cost = Cells(k, 2)
Exit For
End If
Next k
Worksheets("Êîìàíäèðîâêè").Activate
Dim test As Integer
Cells(Application.WorksheetFunction.Match(Cells(j, 7), Range("A1:A298"), 0), 8) = Cells(Application.WorksheetFunction.Match(Cells(j, 7), Range("A1:A298"), 0), 8) - real_cost
End If
Worksheets("Êîìàíäèðîâêè").Activate
Next j
Worksheets("Êîìàíäèðîâêè").Activate
For j = 2 To 298
Cells(j, 8) = ""
Next j
Sheets.Add After:=Sheets(Sheets.count)
Sheets(Sheets.count).Select
Sheets(Sheets.count).Name = "Íåðåíòàáåëüíûå êîìàíäèðîâêè"
Worksheets("Ñòîèìîñòü êîìàíäèðîâîê").Activate
id = 1
For j = 1 To 295
If Cells(j, 3) < 0 Then
business_trip = Cells(j, 1)
Worksheets("Íåðåíòàáåëüíûå êîìàíäèðîâêè").Activate
Cells(id, 1) = business_trip
id = id + 1
End If
Worksheets("Ñòîèìîñòü êîìàíäèðîâîê").Activate
Next j
End Sub