☰

用于根据条件检查日期范围的宏

tag-icon • tag-icon microsoft-excel vba vlookup
用于根据条件检查日期范围的宏

我需要 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

相关内容