Все специалисты - Âñå ñïåöèàëèñòû

 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
Function AddListOfWorkers(Emploees_Array As Dictionary)
' Enter form sheets list og workers
Enter1 = 2
Enter2 = 2
Enter3 = 2
Enter4 = 2
' Design from sheets list of workers
Worksheets("Все специалисты").Rows(1).Interior.Color = RGB(190, 203, 242)
Worksheets("Все специалисты").Cells(1, 2).ColumnWidth = 20
Worksheets("Все специалисты").Cells(1, 3).ColumnWidth = 15
Worksheets("Все специалисты").Cells(1, 7).ColumnWidth = 20
Worksheets("Все специалисты").Cells(1, 8).ColumnWidth = 15
Worksheets("Все специалисты").Cells(1, 12).ColumnWidth = 20
Worksheets("Все специалисты").Cells(1, 13).ColumnWidth = 15
Worksheets("Все специалисты").Cells(1, 17).ColumnWidth = 20
Worksheets("Все специалисты").Cells(1, 18).ColumnWidth = 15
' Add title from sheets list of workers
Worksheets("Все специалисты").Cells(Enter1 - 1, 2) = "Переводчики"
Worksheets("Все специалисты").Cells(Enter1 - 1, 7) = "Инженеры"
Worksheets("Все специалисты").Cells(Enter1 - 1, 12) = "Технологи"
Worksheets("Все специалисты").Cells(Enter1 - 1, 17) = "Остальные"
' Form a list of employees for posts
For i = 2 To Worksheets("Кто едет").Cells(1, 1).CurrentRegion.Rows.Count
If (Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) Like "*переводчик*") Then
Worksheets("Все специалисты").Cells(Enter1, 1) = Worksheets("Кто едет").Cells(i, 1)
Worksheets("Все специалисты").Cells(Enter1, 2) = Worksheets("Кто едет").Cells(i, 2)
Enter1 = Enter1 + 1
End If
If (Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) = "инженер") Then
Worksheets("Все специалисты").Cells(Enter2, 6) = Worksheets("Кто едет").Cells(i, 1)
Worksheets("Все специалисты").Cells(Enter2, 7) = Worksheets("Кто едет").Cells(i, 2)
Enter2 = Enter2 + 1
End If
If (Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) = "технолог") Then
Worksheets("Все специалисты").Cells(Enter3, 11) = Worksheets("Кто едет").Cells(i, 1)
Worksheets("Все специалисты").Cells(Enter3, 12) = Worksheets("Кто едет").Cells(i, 2)
Enter3 = Enter3 + 1
End If
If (Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) = "строитель") Or _
(Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) = "начальник участка") Or _
(Emploees_Array.Item(Worksheets("Кто едет").Cells(i, 2).Value) = "прораб") Then
Worksheets("Все специалисты").Cells(Enter4, 16) = Worksheets("Кто едет").Cells(i, 1)
Worksheets("Все специалисты").Cells(Enter4, 17) = Worksheets("Кто едет").Cells(i, 2)
Enter4 = Enter4 + 1
End If
Next i
For i = 2 To Worksheets("Âñå ñïåöèàëèñòû").Cells(1, 1).CurrentRegion.Rows.Count
For j = 2 To Worksheets("Êîìàíäèðîâêè").Cells(1, 1).CurrentRegion.Rows.Count
If Worksheets("Êîìàíäèðîâêè").Cells(j, 1) = Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 1) Then
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 3) = Worksheets("Êîìàíäèðîâêè").Cells(j, 2).Value
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 4) = Worksheets("Êîìàíäèðîâêè").Cells(j, 3)
End If
Next
Next
For i = 2 To Worksheets("Âñå ñïåöèàëèñòû").Cells(1, 6).CurrentRegion.Rows.Count
For j = 2 To Worksheets("Êîìàíäèðîâêè").Cells(1, 1).CurrentRegion.Rows.Count
If Worksheets("Êîìàíäèðîâêè").Cells(j, 1) = Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 6) Then
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 8) = Worksheets("Êîìàíäèðîâêè").Cells(j, 2).Value
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 9) = Worksheets("Êîìàíäèðîâêè").Cells(j, 3)
End If
Next
Next
For i = 2 To Worksheets("Âñå ñïåöèàëèñòû").Cells(1, 11).CurrentRegion.Rows.Count
For j = 2 To Worksheets("Êîìàíäèðîâêè").Cells(1, 1).CurrentRegion.Rows.Count
If Worksheets("Êîìàíäèðîâêè").Cells(j, 1) = Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 11) Then
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 13) = Worksheets("Êîìàíäèðîâêè").Cells(j, 2).Value
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 14) = Worksheets("Êîìàíäèðîâêè").Cells(j, 3)
End If
Next
Next
For i = 2 To Worksheets("Âñå ñïåöèàëèñòû").Cells(1, 16).CurrentRegion.Rows.Count
For j = 2 To Worksheets("Êîìàíäèðîâêè").Cells(1, 1).CurrentRegion.Rows.Count
If Worksheets("Êîìàíäèðîâêè").Cells(j, 1) = Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 16) Then
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 18) = Worksheets("Êîìàíäèðîâêè").Cells(j, 2).Value
Worksheets("Âñå ñïåöèàëèñòû").Cells(i, 19) = Worksheets("Êîìàíäèðîâêè").Cells(j, 3)
End If
Next
Next
End Function