我需要 VBA 代码来对照另一张表中的表格检查日期范围内的每个工作日日期,如果任何一天的对应值 >9%,它应该返回一个消息框
即,在“休假申请模板”中,代码应检查 B7 和 B9 中给出的范围内的每个工作日,对照“休假追踪主表”中的“主”工作表,如果“总计”列中任何一个日期的对应值 >9%,则应返回一个消息框
我已经为其编写了以下代码,但是在运行代码时出现类型不匹配错误(代码 Vlookup 行中的错误)
Dim wsDest As Worksheet
Dim wsCheck As Worksheet
Dim EmailApp As Outlook.Application
Dim EmailItem As Outlook.MailItem
Dim leaveDate As Date
Dim st As Date
Dim lDestLastRow As Long
Set EmailApp = New Outlook.Application
Set EmailItem = EmailApp.CreateItem(olMailItem)
Set wsCopy = ThisWorkbook.Worksheets("Sheet1")
If wsCopy.Range("C4") = "" Or wsCopy.Range("B7") = "" Or wsCopy.Range("B9") = "" Or wsCopy.Range("B13") = "" Then
MsgBox "Please enter all required fields "
Exit Sub
Else
Set wsCheck = Workbooks.Open("G:\Manager Folder\Test\Leave Tracker Master.xlsx").Worksheets("Master")
st = wsCopy.Range("B7").Value
Set myrange = wsCheck.Range("A:D")
Do While st <= wsCopy.Range("B9").Value
If (Application.VLookup(st, myrange, 4, False) > 0.09) Then
MsgBox "Leave cannot be Applied"
Exit Sub
End If
st = st + 1
Loop
Set wsDest = Workbooks.Open("G:\Manager Folder\Test\Leave Tracker Master.xlsx").Worksheets("Tracker")
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
leaveDate = wsCopy.Range("B7").Value
Do While leaveDate <= wsCopy.Range("B9").Value
If (leaveDate Mod 7) > 1 Then
wsDest.Range("A" & lDestLastRow) = wsCopy.Range("C4").Value
wsDest.Range("B" & lDestLastRow) = wsCopy.Range("E4").Value
wsDest.Range("C" & lDestLastRow) = wsCopy.Range("G4").Value
wsDest.Range("D" & lDestLastRow) = wsCopy.Range("B1").Value
wsDest.Range("E" & lDestLastRow) = leaveDate
wsDest.Range("F" & lDestLastRow) = wsCopy.Range("B13").Value
lDestLastRow = lDestLastRow + 1
End If
leaveDate = leaveDate + 1
Loop
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub
答案1
datevalue 函数解决了这个问题
Do While st <= wsCopy.Range("B9").Value
If (Application.WorksheetFunction.VLookup(st, myrange, 4, False) >= 0.09) Then