|
Private Sub Report_Click()
Dim input_report As String
Dim long_input As Long
Dim serrial_project As Integer: serrial_project = 0
If Date_Month.Text = "" Then
MsgBox "Please input Report Date !"
End
End If
input_report = Date_Month.Text
long_input = CLng(CDate(input_report)) 'change date to long
While (Sheets("Month").Cells(serrial_project + 5, 3).Value <> "")
Call each_project(serrial_project, long_input)
serrial_project = serrial_project + 1
Wend
End Sub
'
Private Function each_project(a, b)
Dim j, k As Integer: k = 0
Dim sourch_report As String
Dim p1, p2 As Integer
Dim q1, q2 As String
Dim long_source As Long
Dim sum_ports As Integer: sum_ports = 0
For j = 1 To Sheets.Count
If UCase(Sheets(j).Name) = UCase(Sheets("month").Cells(a + 5, 3).Value) Then
While (UCase(Sheets(j).Cells(4 + k, 13).Value) <> "")
If (UCase(Sheets(j).Cells(4 + k, 13).Value) = "YES") Then
sourch_report = Sheets(j).Cells(4 + k, 10).Value
p1 = InStr(sourch_report, "/")
p2 = InStrRev(sourch_report, "/")
q1 = Left(sourch_report, p1 - 1)
q2 = Right(sourch_report, Len(sourch_report) - p2)
long_source = CLng(CDate(q1 & "/" & q2))
If long_source = b Then
sum_ports = sum_ports + Val(Sheets(j).Cells(4 + k, 11).Value)
End If
End If
k = k + 1
Wend
Sheets("month").Cells(a + 5, 5).Value = sum_ports
sum_ports = 0: k = 0
Exit For
End If
Next j
End Function |
|