我如何安排时间在VBA上的时间表 [英] how do I arrange time for a schedule on vba

问题描述

我正在Excel中制定此计划，这将使我知道是否仅按特定的班次安排一个人.因此，该宏的作用是遍历我已附加的特定列表，并检查它们的开始时间和结束时间.我希望宏通知我是否在某个特定时间安排某人，如果某人会重叠.以下代码可以正常运行，但是现在我正在测试以查看是否可以正常运行，我输入了一个名称，它们将在运行的应用服务器，开始时间和结束时间.因此，宏经过一个循环以检查我在excel上提供的表，但是我在时间上遇到了错误.例如，如果我将开始时间设为上午6:00，结束时间设为上午6:30，并在桌上摆放，前提是我让其他人在5:59到rowendtime = 6:35的行时间运行该应用程序它应该显示冲突"，因为它们重叠，但由于某种原因它显示无冲突"，我们将不胜感激.这是代码:

I am working on this schedule in Excel that will let me know if am scheduling only one person at a specific shift. So what the macro does it goes over a specific list that I have attached and checks their starting time and ending time. I want the macro to let me know if I schedule someone at a specific time that if there will be overlapping someone else. The following code is working, but now that I am testing to see if it works I put a name, app server they will be running, starting time and ending time. So the macro goes over a loop to check the table that I provide on excel, but I'm getting an error on the time. For example if I put the starting time at 6:00 am and the ending time at 6:30 am and on the table provided I have someone else running that app at rows tart time at 5:59 am to rowendtime= 6:35 am it should show me a "conflict" because they are overlapping but for some reason it is showing "no conflict" Any help would be really appreciated. Here is the code:

Public Sub LoopRows(Appserver As String, StartTime As Date, EndTime As Date)

  Dim x As Integer
  Dim NumROws As Integer
  NumROws = Range("Sheet3!C5").End(xlDown).Row - Range("Sheet3!C5").Row
  Worksheets("Sheet3").Activate
  Range("Sheet3!C5").Select
  msgbox Appserver
  msgbox StartTime
  msgbox EndTime
  For x = 1 To NumROws
    ActiveCell.Offset(1, 0).Select
    If (ActiveCell.Offset(0, 1).Value = Appserver) Then
         Dim RowStartTime As Date
         Dim RowEndTime As Date
         msgbox ActiveCell.Offset(0, 1).Value
         msgbox RowStartTime = Val(ActiveCell.Offset(0, 3).Value)
         msgbox RowEndTime = Val(ActiveCell.Offset(0, 4).Value)
         If (((EndTime > RowStartTime) And (EndTime < RowEndTime)) Or (((StartTime > RowStartTime) And (StartTime < RowEndTime))) Or (((StartTime < RowStartTime) And (EndTime > RowEndTime)))) Then
            msgbox "Conflict"
         Else
            msgbox "noConflict"
         End If
     End If
  Next
End Sub

推荐答案

简化您的 If 条件.两个时间跨度不可能重叠，而一个起始时间不会落在另一个跨度的开始和结束之间:

Simplify your If condition. It's impossible for two time spans to overlap without one starting time falling between the other span's start and end:

部分重叠的情况:

Start1                        End1
  |----------------------------|
              Start2                 End2
                |---------------------|

完全重叠的情况:

Start1                        End1
  |----------------------------|
      Start2            End2
        |----------------|

您可以使 If 条件如下:

If (StartTime > RowStartTime) And (StartTime < RowEndTime) Or _
   (RowStartTime > StartTime) And (RowStartTime < EndTime) Then

编辑:它看起来应该更像下面的代码.请注意，我从原始代码中假设"Appserver"值在"F"列中，开始时间在"H"列中，结束时间在"I"列中.在 CDate 调用上也没有错误检查(但是同样在 Val 调用上也没有错误).

It should look more like the code below. Note that I'm assuming from the original code that the "Appserver" value is in column "F", starting times are in column "H", and ending times are in column "I". There is also no error checking on the CDate calls (but then again there wasn't on the Val calls either).

Public Sub LoopRows(Appserver As String, StartTime As Date, EndTime As Date)
    Dim CurrentRow As Integer, LastRow As Integer
    Dim RowStartTime As Date, RowEndTime As Date
    With Worksheets("Sheet3")
        LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
        For CurrentRow = 6 To LastRow
            If (.Cells(CurrentRow, 6).Value = Appserver) Then
                RowStartTime = CDate(.Cells(CurrentRow, 8).Value)
                RowEndTime = CDate(.Cells(CurrentRow, 9).Value)
                If (StartTime > RowStartTime) And (StartTime < RowEndTime) Or _
                   (RowStartTime > StartTime) And (RowStartTime < EndTime) Then
                    MsgBox "Conflict"
                Else
                    MsgBox "noConflict"
                End If
            End If
        Next
    End With
End Sub

这篇关于我如何安排时间在VBA上的时间表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！