|
代码1如下:这一共有8个一样的判断的,表内数据有3000行,运行起来要好几分钟,那个累!求高手给点好的建议,我这个能实现功能就是慢。
Sub 过期提醒()
Dim a, b, c, d, MST As String, h, i, x, y, z, g, m
c = Sheet2.[P1] '设定科目一时间
d = Sheet2.[R1] '设定分配教练时间
h = Sheet2.[H1] '设定科目二时间
i = Sheet2.[i1] '设定科目三时间
x = Sheet2.[X1] '设定科目四时间
y = Sheet2.[Y1] '设定身份证过期时间
z = Sheet2.[Z1] '设定驾驶证过期时间
g = Sheet2.[g1] '设定技能证过期时间
For i = 3 To Sheet2.UsedRange.Rows.Count
'==================================================科目一提醒
If Sheet2.[O1] = 1 Then
If Sheet2.Cells(i, 3).Value > 0 And Sheet2.Cells(i, 13).Value = "" And Sheet2.Cells(i, 4).Value > 0 And Sheet2.Cells(i, 6).Value > 0 Then '判断《报名时间列是否有报名时间》 和 《是否安排电脑考试》
a = Date - Sheet2.Cells(i, 3).Value '计算报名时间距离当天有多久
If a > c Then '报名时间超过《设定时间》弹出窗口提醒!
' 'MsgBox "《 " & Sheet2.Cells(i, 4).Value & " 》" & "已经报名" & a & "天了,请尽快了解情况并安排电脑考试!" & Chr(10) & "" & Chr(10) & "身份证号码:" & Sheet2.Cells(i, 6).Value & Chr(10) & Chr(10) & "" & "联系电话:" & Sheet2.Cells(i, 8).Value, 48, "温馨提示窗口"
aa = "已经报名" & a & "天了,请尽快了解情况并安排电脑考试!"
Sheet15.Select
x = Range("A65536").End(xlUp).Row + 1 '最后行数
Cells(x, 1) = x - 1
Range("B" & x & ":F" & x) = Array(Sheet2.Cells(i, 3).Value, Sheet2.Cells(i, 4).Value, Sheet2.Cells(i, 6).Value, Sheet2.Cells(i, 8).Value, aa)
Sheet2.Select
End If
End If
End If
Next i
End Sub
代码2如下:这个总的有6个一样的语句一起判断的,也是功能能实现就是慢。。。
Sub 判断错误()
Dim MST As String, MST1 As String, MST2 As String, MST3 As String, MST4 As String, MST5 As String, MST6 As String
For i = 3 To Sheet2.UsedRange.Rows.Count
'==================================================判断科目一考试时间是否错误
If Sheet2.Cells(i, 13).Value > 0 Then
For x = 1 To 1
Select Case True
Case Application.IsText(Sheet2.Cells(i, 13))
MST = "文本"
Case Application.IsLogical(Sheet2.Cells(i, 13))
MST = "逻辑值"
Case IsNumeric(Sheet2.Cells(i, 13))
MST = "数值"
Case Application.IsErr(Sheet2.Cells(i, 13))
MST = "错误值"
End Select
Next x
If MST = "文本" Or MST = "逻辑值" Or MST = "数值" Or MST = "错误值" Then
''MsgBox "注意:《" & Sheet2.Cells(i, 4).Value & "》的科一时间格式错误,请修改正确的日期!", 16, "温馨提示窗口"
aa = "科一时间格式错误,请修改正确的日期!"
Sheet15.Select
x = Range("A65536").End(xlUp).Row + 1 '最后行数
Cells(x, 1) = x - 1
Range("B" & x & ":F" & x) = Array(Sheet2.Cells(i, 3).Value, Sheet2.Cells(i, 4).Value, Sheet2.Cells(i, 6).Value, Sheet2.Cells(i, 8).Value, aa)
Sheet2.Select
End If
If Sheet2.Cells(i, 3).Value > Sheet2.Cells(i, 13).Value Then
''MsgBox "注意:《" & Sheet2.Cells(i, 4).Value & "》的科一时间比报名时间早,请修改正确的日期!", 16, "温馨提示窗口"
aa = "科一时间比报名时间早,请修改正确的日期!"
Sheet15.Select
x = Range("A65536").End(xlUp).Row + 1 '最后行数
Cells(x, 1) = x - 1
Range("B" & x & ":F" & x) = Array(Sheet2.Cells(i, 3).Value, Sheet2.Cells(i, 4).Value, Sheet2.Cells(i, 6).Value, Sheet2.Cells(i, 8).Value, aa)
Sheet2.Select
End If
End If
Next i
MsgBox "Holle!检查完毕,请到异常明细表查看结果并及时修改,以免造成数据错乱!", 64, "温馨提示窗口"
End Sub
学员总表(综合版).rar
(1.96 MB, 下载次数: 5)
VBA密码:123456 以上2个代码在附件里面的模块3内,因为比较长我就没有全部贴出来,求高手来指导下
激活密码是:当天日期*9+2661092 例如:20160209*9+2661092=184102973
|
|
|