Sub Çàäàíèå1 Îáúÿâëÿåì ëèñòû Set Trips Worksheets Êîìàíäèðîâêè Set Goa

 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
Sub Çàäàíèå1()
' Îáúÿâëÿåì ëèñòû
Set Trips = Worksheets("Êîìàíäèðîâêè")
Set Goals = Worksheets("Ñïèñîê öåëåé")
' Îáúÿâëÿåì àññîöèàòèâíûé ìàññèâ
Dim Goal_Array As New Dictionary
' Îáúÿâëÿåì äëèíû ëèñòîâ
Dim Goal_Row, Trip_Row As Integer
' Ïîëó÷àåì äëèíû ëèñòîâ
Goal_Row = Goals.Cells(2, 1).CurrentRegion.Rows.count
Trip_Row = Trips.Cells(2, 1).CurrentRegion.Rows.count
' Çàïîëíÿåì àññîöèàòèâíûé ìàññèâè
For i = 2 To Goal_Row
Goal_Array.Add Goals.Cells(i, 1).Value, Goals.Cells(i, 2).Value
Next i
' Ïåðåáèðàåì âñå ñðîêè êîìàíäèðîâêè
For i = 2 To Trip_Row
' Ïðîâåðêà öåëè êîìàíäèðîâêè
If Goal_Array.Item(Trips.Cells(i, 4).Value) Then
' Ïðîâåðêà ìàêñèìàëüíîãî ñðîêà êîìàíäèðîâêè
If (CInt(Goal_Array.Item(Trips.Cells(i, 4).Value)) < CInt(Trips.Cells(i, 3))) Then
' Îêðàøèâàåì îøèáêè
Trips.Cells(i, 3).Interior.Color = RGB(255, 219, 190)
' Èçìåíÿåì ñðîêè íà äîïóùåííûå
Trips.Cells(i, 3) = Goal_Array.Item(Trips.Cells(i, 4).Value)
End If
End If
Next
End Sub