Ticket-Generation/Module4.bas at main · DRted2099/Ticket-Generation · GitHub

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
Attribute VB_Name = "Module4"
Option Explicit

Private Sub KeyList()

Dim routeSplit() As String, varE() As String, cellTot() As String
Dim minRoute As Integer, i As Integer, l As Integer
Dim rng As Range, rng2 As Range
Dim var1 As String, var2 As String, var3 As String
Dim varEInput As Integer

Call Module3.RouteNos

var3 = "A1"
var1 = "A3"

For i = 0 To UBound(noRoutes) - 1

    routeSplit = Split(noRoutes(i), "-")

    minRoute = Min(routeSplit)

    Debug.Print (minRoute)

    Worksheets(5).Select

    Range("A3").CurrentRegion.Select

    Set rng = Selection.Find("R" & minRoute, After:=Range(var1), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlRows)

    Set rng2 = Selection.Find("R" & minRoute & " Total", After:=Range(var1), LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlRows)

    var1 = rng.Offset(0, 3).Address

    Debug.Print var1

    If rng2 Is Nothing Then

        Set rng2 = rng

    End If

    var2 = rng2.Address

    rng.Select
    Selection.Offset(0, 3).Select

    Debug.Print var1, var2

    If rng = rng2 Then

        varE = Module3.Trial_Func("1")

    Else

        Debug.Print var2
        cellTot = Split(var2, "$")
        Range(var1 & ":" & "E" & cellTot(2) - 1).Select
        varEInput = Selection.SpecialCells(xlCellTypeConstants).Count

        Range(var1).Select


        varE = Module3.Trial_Func(CStr(varEInput))

    End If

    'Debug.Print UBound(varE), varE(0)

    Worksheets(4).Select
    Range(var3).Select

    Selection.CurrentRegion.Select

    'Debug.Print "Route " & "0" & noRoutes(i)

    If minRoute < 10 Then
        Set rng2 = Selection.Find("0" & minRoute, After:=Range(var3), LookIn:=xlValues, LookAt:=xlPart, _
                        SearchOrder:=xlRows)

    Else

        Set rng2 = Selection.Find(minRoute, After:=Range(var3), LookIn:=xlValues, LookAt:=xlPart, _
                        SearchOrder:=xlRows)

    End If


    Debug.Print rng2.Address

    rng2.Select

    var3 = Selection.End(xlDown).Address

    Selection.Offset(1, 0).Select

    For l = 0 To UBound(varE) - 1

        Selection.Offset(l, 0).Value = varE(l)

        Next l

    Selection.Offset(l, 0).Select

    Worksheets(5).Select

Next i


End Sub

Function Min(arr_string() As String)

Dim i As Integer, minVal As String

minVal = arr_string(0)

If UBound(arr_string) > 0 Then

    For i = 0 To UBound(arr_string)

        If CInt(arr_string(i)) < CInt(minVal) Then

            minVal = arr_string(i)

        End If

    Next i

End If

Min = minVal

End Function