|
代码2,第2部分:
- Sub CheckDate()
- '---------------------------------------
- ' 检查数据
- '---------------------------------------
- Dim ErrXX As String '错误信息文本
- Dim ErrXXTotal As String
- Dim ShtNameList As String '要检测的工作表名称字符串
- Dim ShtExist As Boolean 'Ture=工作表存在
- Dim ShtName() As String '各个工作表的名称数组
- ' 定义行列数变量
- Dim MaxRow As Long, MaxCol As Long
- Dim RsArr() As Variant ' 定义基础数组:人事总表
- Dim GjArr() As Variant ' 定义基础数组:工时工价表
- Dim DateArr() As Variant ' 定义基础数组:当前工作表
- Dim ListC As String
- '---------------------------------------
- ' 检查项1:检查工作表名称是否被修改。
- ShtNameList = "人事总表、工时工价表"
- ShtName = Split(ShtNameList, "、")
- ShtExist = False
- For I = 0 To UBound(ShtName)
- For J = 1 To ThisWorkbook.Sheets.Count
- If Sheets(J).Name = ShtName(I) Then
- ShtExist = True
- Exit For
- End If
- Next
- If Not ShtExist Then
- If ErrXX = "" Then ErrXX = "下列程序中使用的工作表名称被修改: " & Chr(10)
- ErrXX = ErrXX & Chr(10) & " " & ShtName(I)
- End If
- ShtExist = False
- Next
- If ErrXX <> "" Then
- MsgBox ErrXX, vbOKOnly + vbCritical, "数据校验"
- End
- End If
- '---------------------------------------
- ' 检查项2:检查工时工价表
- ' 其1:检查标题是否被修改
- ' 将询证函信息导入数组
- With Sheets("工时工价表")
- MaxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column '第1行最后一列
- MaxRow = .Cells(.Rows.Count, 1).End(xlUp).Row '第1列最后一行
- '将原始数据导入数组
- GjArr = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol)).Value
- End With
- TextList = "款号、工序名称、工序组成、工价"
- For I = 1 To UBound(GjArr, 2)
- If InStr(TextList, GjArr(1, I)) = 0 Then
- ErrXX = ErrXX & GjArr(1, I) & "、"
- End If
- Next
- If ErrXX <> "" Then
- MsgBox "工时工价表的标题行存在错误:" & ErrXX, vbCritical, "数据校验"
- End
- End If
- ErrXX = ""
- ' 其2:检查工时工价表中是否存在错误:款号+工序名称存在重复
- For I = 2 To UBound(GjArr)
- If InStr(ListC, GjArr(I, 1) & GjArr(I, 2) & "结束#") > 0 Then
- ' 当前款号+工序名称存在重复
- ErrXX = ErrXX & GjArr(I, 1) & "," & GjArr(I, 2) & Chr(10)
- Else
- ' 当前款号+工序名称存不在重复时
- ListC = ListC & GjArr(I, 1) & GjArr(I, 2) & "结束#" & ","
- End If
- Next
- If ErrXX <> "" Then
- ErrXX = "工时工价表中存在重复的款号+工序名称:" & Chr(10) & ErrXX
- ErrXX = ErrXX & Chr(10) & "请修改。"
- MsgBox ErrXX, vbCritical, "数据校验"
- End
- End If
- ' 其3:检查工时工价表中是否存在错误:工价小于等于0
- ErrXX = ""
- For I = 2 To UBound(GjArr)
- If GjArr(I, 4) <= 0 Then
- ' 当前款号+工序名称的工价小于等于0,存在错误
- ErrXX = ErrXX & GjArr(I, 1) & "," & GjArr(I, 2) & Chr(10)
- End If
- Next
- If ErrXX <> "" Then
- ErrXX = "工时工价表中存在款号+工序名称对应的工价错误情况:" & Chr(10) & ErrXX
- ErrXX = ErrXX & Chr(10) & "请修改。"
- MsgBox ErrXX, vbCritical, "数据校验"
- End
- End If
- ErrXX = ""
- '---------------------------------------
- ' 检查项3:检查人事总表
- ' 其1:检查标题是否被修改
- ' 将询证函信息导入数组
- With Sheets("人事总表")
- MaxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column '第1行最后一列
- MaxRow = .Cells(.Rows.Count, 2).End(xlUp).Row '第2列最后一行
- '将原始数据导入数组
- RsArr = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol)).Value
- End With
- TextList = "工号、姓名、时薪"
- For I = 1 To UBound(RsArr, 2)
- If InStr(TextList, RsArr(1, I)) = 0 Then
- ErrXX = ErrXX & RsArr(1, I) & "、"
- End If
- Next
- If ErrXX <> "" Then
- MsgBox "人事总表的标题行存在错误:" & ErrXX, vbCritical, "数据校验"
- End
- End If
- ' 其2:检查姓名是否存在重复
- ErrXX = ""
- ListC = ""
- For I = 2 To UBound(RsArr)
- If InStr(ListC, RsArr(I, 2) & "结束#") > 0 Then
- ' 当前姓名存在重复
- ErrXX = ErrXX & RsArr(I, 1) & Chr(10)
- Else
- ' 当前款号+工序名称存不在重复时
- ListC = ListC & RsArr(I, 1) & "结束#" & ","
- End If
- Next
- If ErrXX <> "" Then
- MsgBox "人数总表中存在重复的姓名:" & Chr(10) & ErrXX, vbCritical, "数据校验"
- End
- End If
- ErrXX = ""
- '---------------------------------------
- ' 检查项4:检查当前工作表文员录入的数据:款号、姓名、工作内容描述(工序)
- With ActiveSheet
- '---------------------------------------
- ' 提取基础数据
- ' 确认文员输入区的起始行和结束行
- MaxRow = .Cells(.Rows.Count, 2).End(xlUp).Row '第2列最后一行
- For I = 9 To MaxRow
- ' 规定生产工人数据行区域至少从第9行开始
- If .Cells(I, 2) = "款号" Then
- KSRow = I + 1
- Exit For
- End If
- Next
- MaxCol = .Cells(I, .Columns.Count).End(xlToLeft).Column '第i行最后一列
- ' 将数据写入数组
- DateArr = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol)).Value '将业绩表的内容导入到数组中
- ' 取得生产工人数据区各列的列次
- For J = 2 To MaxCol
- Select Case .Cells(I, J)
- Case "款号"
- KhCol = J
- Case "姓名"
- XmCol = J
- Case "工作内容描述"
- NrCol = J
- End Select
- Next
- ' 沿着 款号 列找到结束行
- For J = KSRow To MaxRow
- ' 沿着款号列向下,下一行为空或前两个字是小计,则本行是结束计算行
- If .Cells(J + 1, KhCol) = "" Or Left(.Cells(J + 1, KhCol), 2) = "小计" Then
- JSRow = J
- Exit For
- End If
- Next
- '---------------------------------------
- ' 文员输入区(目前固定为B-D列)取消底色
- With .Range(.Cells(KSRow, 2), .Cells(JSRow, 4)).Interior
- .Pattern = xlNone
- .TintAndShade = 0
- .PatternTintAndShade = 0
- End With
- '---------------------------------------
- ' 其1:检查款号列有无错误
- For J = KSRow To JSRow
- ' 检查当前行的款号是否出现在工时工价表中
- ErrXX = DateArr(J, KhCol)
- For I = 1 To UBound(GjArr)
- If DateArr(J, KhCol) = GjArr(I, getNum(GjArr, "款号")) Then
- ErrXX = ""
- End If
- Next
- If ErrXX <> "" Then
- ErrXXTotal = "错误:第 " & J & " 行;" & " 款号:" & ErrXX
- ' 错误信息单元格着底色
- With .Cells(J, KhCol).Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent2
- .TintAndShade = -0.249977111117893
- .PatternTintAndShade = 0
- End With
- End If
- Next
- '---------------------------------------
- ' 其2:检查姓名列有无错误
- For J = KSRow To JSRow
- ' 检查当前行的款号是否出现在工时工价表中
- ErrXX = DateArr(J, XmCol)
- For I = 1 To UBound(RsArr)
- If ErrXX = RsArr(I, getNum(RsArr, "姓名")) Then
- ErrXX = ""
- End If
- Next
- If ErrXX <> "" Then
- ErrXXTotal = ErrXXTotal & Chr(10) & "错误:第 " & J & " 行;" & " 姓名:" & ErrXX
- ' 错误信息单元格着底色
- With .Cells(J, XmCol).Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent2
- .TintAndShade = -0.249977111117893
- .PatternTintAndShade = 0
- End With
- End If
- Next
- '---------------------------------------
- ' 其3:检查工作内容描述列有无错误
- For J = KSRow To JSRow
- ' 检查当前行的款号是否出现在工时工价表中
- ErrXX = DateArr(J, NrCol)
- If InStr(ErrXX, "计时/") > 0 Then
- ' 是计时工序,整理工序名称
- ErrXX = Split(ErrXX, "计时/")(1)
- End If
- For I = 1 To UBound(GjArr)
- If ErrXX = GjArr(I, getNum(GjArr, "工序名称")) Then
- ErrXX = ""
- End If
- Next
- If ErrXX <> "" Then
- ErrXXTotal = ErrXXTotal & Chr(10) & "错误:第 " & J & " 行;" & " 工序:" & ErrXX
- ' 错误信息单元格着底色
- With .Cells(J, NrCol).Interior
- .Pattern = xlSolid
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent2
- .TintAndShade = -0.249977111117893
- .PatternTintAndShade = 0
- End With
- End If
- Next
- If ErrXXTotal <> "" Then
- MsgBox ErrXXTotal & Chr(10) & Chr(10) & "已更改底色着重显示,请修改。"
- End
- End If
- End With
- End Sub
复制代码 |
|