1: Sub Calculate()
2: '
3: ' Calculate Macro
4: '
5: Application.ScreenUpdating = False
6:
7: Dim Row1 As Integer ' to store row number of input file, to perform navigation
8: Dim Col1 As Integer ' to store col number of input file, to perform navigation
9: Dim Row2 As Integer ' to store row number of s tab of output file, to perform navigation
10: Dim Col2 As Integer ' to store col number of s tab of output file, to perform navigation
11: Dim i As Integer ' for loop to navigate through 24 hrs
12: Dim k As Integer ' for loop to navigate through 5 s
13: Dim l As Integer ' for loop to navigate through 7 days
14: Dim Hrs(1 To 24) As Integer ' to identify 24 hrs
15: Dim Path1 As String ' path of the input/output files
16: Dim Filename1(1 To 7) As String ' names of the input files
17: Dim Filename2 As String ' name of the output file
18: Dim Sum1 As Long ' to store sum of SM-Ds of each hr
19: Dim Flag1 As Integer ' to identify if data of any hr is missing
20:
21: For i = 1 To 24 ' to initialize hrs array with hr nums 0 to 23
22: Hrs(i) = i - 1
23: Next i
24:
25: Sheets("S").Select ' to clear previous values of s tab 26: Range("B3:AP26").Select 27: Selection.ClearContents
28: Range("A1").Select 29:
30: Sheets("Main Menu").Select ' to read path and file names 31: Path1 = Range("C2").Text 32: Filename1(1) = Range("D2").Text 33: Filename1(2) = Range("D3").Text 34: Filename1(3) = Range("D4").Text 35: Filename1(4) = Range("D5").Text 36: Filename1(5) = Range("D6").Text 37: Filename1(6) = Range("D7").Text 38: Filename1(7) = Range("D8").Text 39: Filename2 = Range("C10").Text 40:
41: Col2 = 2 ' col num of s tab of output file
42:
43: For l = 1 To 7 ' for loop to navigate through 7 days
44:
45: Workbooks.Open Filename:=Path1 + Filename1(l) ' open each input file one by one
46: Sheets("Throughput per S - table").Select 47: Row1 = 10 ' row number of input file
48: Col1 = 4 ' col number of input file
49: Row2 = 3 ' row number of s tab of output file
50:
51: For k = 1 To 5 ' for loop to navigate through 5 s
52:
53: For i = 1 To 24 ' for loop to navigate through 24 hrs
54:
55: Sum1 = 0 ' to store sum of SM-Ds of each hr
56: Flag1 = 0 ' to identify if data of any hr is missing - 0 means data for that hr is not available
57: Windows(Filename1(l)).Activate
58: Sheets("Throughput per S - table").Select 59:
60: If Cells(Row1, Col1).Value = Hrs(i) Then ' first row of an hr
61:
62: Flag1 = 1 ' to identify if data of any hr is missing - 1 means data for that hr is available
63:
64: If (Left(Cells(Row1, Col1 + 1).Value, 4) = "SM-D") Then ' if SM-D found
65:
66: Sum1 = Sum1 + Cells(Row1, Col1 + 3).Value ' sum SM-D of that hr
67:
68: End If
69:
70: Row1 = Row1 + 1 ' increment row number of input file
71:
72: Do While (Cells(Row1, Col1).Value = 0) And Row1 < 1200 ' 2nd row and onwards of an hr
73:
74: If (Left(Cells(Row1, Col1 + 1).Value, 4) = "SM-D") Then ' if SM-D found
75:
76: Sum1 = Sum1 + Cells(Row1, Col1 + 3).Value ' sum SM-D of that hr
77:
78: End If
79:
80: Row1 = Row1 + 1 ' increment row number of input file
81:
82: Loop
83:
84: End If
85:
86: If Flag1 = 0 Then ' if flag1 is 0 then increment row number of s tab of output file to skip empty hr
87:
88: Row2 = Row2 + 1
89:
90: ElseIf Flag1 = 1 Then ' if flag1 is 1 then put value in output file
91:
92: Windows(Filename2).Activate
93: Sheets("S").Select 94: Cells(Row2, Col2).Value = Sum1
95: Row2 = Row2 + 1 ' increment row number of output file
96:
97: End If
98:
99: Next i ' for loop to navigate through 24 hrs
100:
101: Row1 = Row1 + 1 ' increment row number of input file
102: Col2 = Col2 + 1 ' increment col number of output file
103: Row2 = 3 ' row number of output file
104:
105: Next k ' for loop to navigate through 5 s
106:
107: Col2 = Col2 + 1 ' increment col number of output file
108: Workbooks(Filename1(l)).Close
109:
110: Next l ' for loop to navigate through 7 days
111:
112: Sheets("Main Menu").Select 113: Application.ScreenUpdating = True
114:
115: End Sub
