|
Sub 数据引入() '将处于当日的数据引用到汇总表格中。copyright by chenby
Application.ScreenUpdating = False '程序运行时屏幕锁定
Dim Ms
Dim DSheet1 As Worksheet '某一天的日报表
Dim HSheet1 As Worksheet '汇总报表
Dim d As String '工作表序号变量
Dim i As Integer '循环用常规常规变量
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Mark1 As Boolean '标志变量
Dim Mark2 As Boolean '标志变量
Dim Z1 As Integer '记录汇总表中需要遍历的班组数
Dim Z2 As Integer '记录日报表中需要遍历的行数
Dim Z3 As Integer '记录汇总表遍历开始行
Dim HBz As String '记录汇总表中班组名称
Dim HD As Integer '汇总表中的日期行
Dim HH As Integer '月汇总
Dim DD As Date
Dim HGZE(50) As String '日报表中产出工时值错误的班组
Dim m As Integer
Dim HCZE(50) As String '日报表中为开线或者未填写出勤人数及时间的班组
Dim n As String
Dim HBUf(50) As String '存在但未找到的班组
Dim p As Integer
Dim TsG As String
Dim TsC As String
Dim TsB As String
Set HSheet1 = ThisWorkbook.Sheets("汇总") '将汇总表赋值给HSheet1
d = HSheet1.Cells(1, 14)
Set DSheet1 = ThisWorkbook.Sheets(d) '要录入的工作表赋值给DSheet;
Z1 = HSheet1.Range("A65536").End(xlUp).Row '汇总表的长度初始化
Z2 = DSheet1.Range("A65536").End(xlUp).Row + 20 '日报表长度舒适化
m = 0
n = 0
p = 0
For i = 1 To Z1 '汇总表遍历开始行初始化
If HSheet1.Cells(i, 1) = "合计" Then
Z3 = i
Exit For
End If
Next
For i = Z3 To Z1
If HSheet1.Cells(i, 1) = "班组:" And HSheet1.Cells(i, 2) <> "" Then
HBz = HSheet1.Cells(i, 2)
HD = i + d + 1
HH = i + 31 + 2
Mark2 = False
For j = 1 To Z2
If DSheet1.Cells(j, 2) = HBz Then
Mark2 = True
Mark1 = False
For k = j + 1 To j + Z2
If DSheet1.Cells(k, 1) = "生产班组:" Then
Mark1 = True
End If
If DSheet1.Cells(k, 4) = "小计:" And Mark1 = False Then
If DSheet1.Cells(k, 8) = 0 Or DSheet1.Cells(k, 9) = 0 Then
HSheet1.Cells(HD, 1) = DateSerial(Year(HSheet1.Cells(3, 3)), Month(HSheet1.Cells(3, 3)), d)
HSheet1.Cells(HD, 1).NumberFormatLocal = "m-d;@"
HSheet1.Cells(HD, 2) = DSheet1.Cells(k, 5)
HSheet1.Cells(HD, 3) = DSheet1.Cells(k, 6)
If IsError(DSheet1.Cells(k, 10)) Then
HSheet1.Cells(HD, 4) = ""
Else
HSheet1.Cells(HD, 4) = DSheet1.Cells(k, 10)
End If
HSheet1.Cells(HD, 5) = DSheet1.Cells(k, 8)
HSheet1.Cells(HD, 6) = DSheet1.Cells(k, 9)
HSheet1.Cells(HD, 7) = DSheet1.Cells(k, 14)
HSheet1.Cells(HD, 10) = DSheet1.Cells(k, 19)
HCZE(n) = HBz '为开班或者未填写出勤时间或出勤人数
n = n + 1
Else
'班组日报正文
HSheet1.Cells(HD, 1) = DateSerial(Year(HSheet1.Cells(3, 3)), Month(HSheet1.Cells(3, 3)), d)
HSheet1.Cells(HD, 1).NumberFormatLocal = "m-d;@"
HSheet1.Cells(HD, 2) = DSheet1.Cells(k, 5)
HSheet1.Cells(HD, 3) = DSheet1.Cells(k, 6)
If IsError(DSheet1.Cells(k, 10)) Then '产出工时错误
HSheet1.Cells(HD, 4) = ""
HGZE(m) = HBz
m = m + 1
Else
HSheet1.Cells(HD, 4) = DSheet1.Cells(k, 10)
End If
HSheet1.Cells(HD, 5) = DSheet1.Cells(k, 8)
HSheet1.Cells(HD, 6) = DSheet1.Cells(k, 9)
HSheet1.Cells(HD, 7) = DSheet1.Cells(k, 14)
HSheet1.Cells(HD, 8) = HSheet1.Cells(HD, 3) / HSheet1.Cells(HD, 5) / HSheet1.Cells(HD, 6) '实际UPPH
HSheet1.Cells(HD, 9) = (HSheet1.Cells(HD, 5) * HSheet1.Cells(HD, 6) - HSheet1.Cells(HD, 7)) / HSheet1.Cells(HD, 5) / HSheet1.Cells(HD, 6) '时间利用率
HSheet1.Cells(HD, 10) = DSheet1.Cells(k, 19) '设备工时
HSheet1.Cells(HD, 11) = Int((DateSerial(Year(HSheet1.Cells(3, 3)), Month(HSheet1.Cells(3, 3)), d) - DateSerial(Year(HSheet1.Cells(3, 3)), 1, 0) + Weekday(DateSerial(Year(HSheet1.Cells(3, 3)), 1, 0), vbMonday) + 7 - Weekday(DateSerial(Year(HSheet1.Cells(3, 3)), Month(HSheet1.Cells(3, 3)), d), vbMonday)) / 7)
HSheet1.Cells(HD, 12) = HSheet1.Cells(HD, 10) / HSheet1.Cells(HD, 5) '设备产出率率
HSheet1.Cells(HD, 13) = HSheet1.Cells(HD, 4) * 3600 / 2500 '标准台
HSheet1.Cells(HD, 14) = HSheet1.Cells(HD, 13) / HSheet1.Cells(HD, 5) / HSheet1.Cells(HD, 6) 'UPPH(标准台)
'班组月度累计
HSheet1.Cells(HH, 2) = 0
HSheet1.Cells(HH, 3) = 0
HSheet1.Cells(HH, 4) = 0
HSheet1.Cells(HH, 5) = 0
HSheet1.Cells(HH, 6) = 0
HSheet1.Cells(HH, 7) = 0
HSheet1.Cells(HH, 10) = 0
For l = i + 2 To i + 32
HSheet1.Cells(HH, 2) = HSheet1.Cells(HH, 2) + HSheet1.Cells(l, 2) '合计排产量
HSheet1.Cells(HH, 3) = HSheet1.Cells(HH, 3) + HSheet1.Cells(l, 3) '合计实际产量
HSheet1.Cells(HH, 4) = HSheet1.Cells(HH, 4) + HSheet1.Cells(l, 4) '合计产出工时
HSheet1.Cells(HH, 5) = HSheet1.Cells(HH, 5) + HSheet1.Cells(l, 5) '合计出勤工时,不计算人次
HSheet1.Cells(HH, 6) = HSheet1.Cells(HH, 6) + HSheet1.Cells(l, 5) * HSheet1.Cells(l, 6) '合计出勤工时,计算人次
HSheet1.Cells(HH, 7) = HSheet1.Cells(HH, 7) + HSheet1.Cells(l, 7) '合计影响工时
HSheet1.Cells(HH, 10) = HSheet1.Cells(HH, 10) + HSheet1.Cells(l, 10) '合计设备工时
Next
'班组月度累计计算
If HSheet1.Cells(HH, 5) > 0 Then
HSheet1.Cells(HH, 8) = HSheet1.Cells(HH, 3) / HSheet1.Cells(HH, 6) '合计实际UPPH
HSheet1.Cells(HH, 9) = (HSheet1.Cells(HH, 6) - HSheet1.Cells(HH, 7)) / HSheet1.Cells(HH, 6) '合计时间利用率
HSheet1.Cells(HH, 12) = HSheet1.Cells(HH, 10) / HSheet1.Cells(HH, 5) '设备产出率
HSheet1.Cells(HH, 13) = HSheet1.Cells(HH, 4) * 3600 / 2500 '标准台
HSheet1.Cells(HH, 14) = HSheet1.Cells(HH, 13) / HSheet1.Cells(HH, 6) 'UPPH(标准台)
End If
End If
Exit For
End If
Next
If Mark1 = True Then
Ms = MsgBox("“" + HBz + "” 的小计行缺失,请检查是否符合要求!", 1, "警告")
If Ms = 2 Then
GoTo F1
End If
End If
End If
Next
If Mark2 = False Then
HBUf(p) = HBz
p = p + 1
End If
End If
Next
TsG = xlnull
TsC = xlnull
TsB = xlnull
For i = 0 To m - 1
TsG = TsG + "“" + HGZE(i) + "”"
Next
For i = 0 To n - 1
TsC = TsC + "“" + HCZE(i) + "”"
Next
For i = 0 To p - 1
TsB = TsB + "“" + HBUf(i) + "”"
Next
Ms = MsgBox("以下班组产出工时错误,请检查:" & vbCrLf & TsG & vbCrLf & vbCrLf & "以下班组未开班或“出勤时间”及“出勤人数”为零:" & vbCrLf & TsC & vbCrLf & vbCrLf & "以下班组在 “" + d + "” 号的生产日报中未找到:" & vbCrLf & TsB, vbDefaultButton2)
F1:
End Sub
|
|