计算VBA网络天差异

Question

i有一个userform，它查看16个情况，并从tab的情况下决定将数据放置在工作簿中。我最近发现了一个问题，应该在其初始编码期间预见到。我要花2个日期来解决它们之间有几天的时间，但是我需要解决的是2之间有多少个工作日。我想它需要在select case方案中进行更改

Private Sub EnterDetails_Click()
Dim mRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim Nextnum As Long
Dim Xnum As Long
Set ws1 = Worksheets("MasterData")
Set ws2 = Worksheets("X")
Set ws3 = Worksheets("A")
Set ws4 = Worksheets("C")
Nextnum = GetNextId(Sheets("MasterData"), "A")
Xnum = GetNextId(Sheets("X"), "AB")
Dim TargetWorksheets As Variant
'16 qualifying scenarios to determine where the data will be sent
    Select Case True
        Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 1: TargetWorksheets = Array(ws1, ws2, ws3)
        Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: TargetWorksheets = Array(ws1, ws2, ws3)
        Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 1: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: TargetWorksheets = Array(ws1, ws4)
        Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 3: TargetWorksheets = Array(ws1, ws2, ws3)
        Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 3: TargetWorksheets = Array(ws1, ws2, ws4)
        Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 3: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 3: TargetWorksheets = Array(ws1, ws4)
        Case ComboPD.Value = "N" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 1: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "N" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: TargetWorksheets = Array(ws1, ws4)
        Case ComboPD.Value = "N" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 1: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "N" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: TargetWorksheets = Array(ws1, ws4)
        Case ComboPD.Value = "N" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 3: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "N" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 3: TargetWorksheets = Array(ws1, ws4)
        Case ComboPD.Value = "N" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 3: TargetWorksheets = Array(ws1, ws3)
        Case ComboPD.Value = "N" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) < 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: TargetWorksheets = Array(ws1, ws4)
        Case Else: TargetWorksheets = Array(ws1)
    End Select
    For Each ws In TargetWorksheets
    'find first empty row in worksheets
        mRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    'copy userform info data to the qualifying database sheets
        ws.Cells(mRow, 1).Value = Nextnum
        ws.Cells(mRow, 2).Value = Format(Date, "YYYY/MM/DD")
        ws.Cells(mRow, 3).Value = Format(Time, "HH:MM:SS")
        ws.Cells(mRow, 4).Value = CInt(Format(Date, "WW"))
        ws.Cells(mRow, 5).Value = DateSerial(Year(ws.Cells(mRow, 2)), Month(ws.Cells(mRow, 2)), 1)
        ws.Cells(mRow, 6).Value = CInt(Format(Date, "YYYY"))
        ws.Cells(mRow, 7).Value = 1
        ws.Cells(mRow, 8).Value = TxtWt.Value * (1300 / 1000)
        ws.Cells(mRow, 9).Value = Application.WorksheetFunction.VLookup(ComboBrd.Value, Sheets("Lookup Vals").Range("G:H"), 2, False)
        ws.Cells(mRow, 10).Value = Application.UserName
                   If ComboBrd.Value = "Myson" Then ws.Cells(mRow, 11).Value = Application.WorksheetFunction.VLookup(ComboCom.Value, Sheets("Lookup Vals").Range("L:N"), 2, False) Else
                   If ComboBrd.Value = "Purmo" Then ws.Cells(mRow, 11).Value = Application.WorksheetFunction.VLookup(ComboCom.Value, Sheets("Lookup Vals").Range("P:R"), 2, False) Else
                       If ComboBrd.Value = "Vogel & Noot" Then ws.Cells(mRow, 11).Value = Application.WorksheetFunction.VLookup(ComboCom.Value, Sheets("Lookup Vals").Range("P:R"), 2, False)
        ws.Cells(mRow, 12).Value = Format(Me.TxtRD.Value, "YYYY/MM/DD")
        ws.Cells(mRow, 13).Value = ComboPD.Value
        ws.Cells(mRow, 14).Value = ComboNP.Value
        ws.Cells(mRow, 15).Value = ComboBrd.Value
        ws.Cells(mRow, 16).Value = ComboCom.Value
        ws.Cells(mRow, 17).Value = TxtAdditional.Value
        ws.Cells(mRow, 18).Value = Format(Me.TxtDD.Value, "YYYY/MM/DD")
        ws.Cells(mRow, 19).Value = TxtBn.Value
        ws.Cells(mRow, 20).Value = TxtFS.Value
        ws.Cells(mRow, 21).Value = ComboPrGp.Value
        ws.Cells(mRow, 22).Value = ComboIss.Value
        ws.Cells(mRow, 23).Value = TxtUn.Value
        ws.Cells(mRow, 24).Value = TxtWt.Value
        ws.Cells(mRow, 25).Value = TxtIn.Value
        ws.Cells(mRow, 26).Value = TxtDetails.Value
        ws.Cells(mRow, 27).Value = TxtSp.Value
    Select Case True
            Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 1: ws.Cells(mRow, 28).Value = Xnum
            Case ComboPD.Value = "Y" And ComboNP.Value = "Y" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 1: ws.Cells(mRow, 28).Value = Xnum
            Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) <= 3: ws.Cells(mRow, 28).Value = Xnum
            Case ComboPD.Value = "Y" And ComboNP.Value = "N" And TxtWt.Value * (1300 / 1000) >= 50 And DateValue(Me.TxtRD.Value) - DateValue(Me.TxtDD.Value) > 3: ws.Cells(mRow, 28).Value = Xnum
    End Select
       Next ws
            TxtRD.Value = ""
            ComboBrd.Value = ""
            ComboPD.Value = ""
            ComboNP.Value = ""
            ComboBrd.Value = ""
            ComboCom.Value = ""
            TxtAdditional.Value = ""
            TxtDD.Value = ""
            TxtBn.Value = ""
            TxtFS.Value = ""
            ComboPrGp.Value = ""
            ComboIss.Value = ""
            TxtUn.Value = ""
            TxtWt.Value = ""
            TxtIn.Value = ""
            TxtDetails.Value = ""
            TxtSp.Value = ""
        ActiveWorkbook.Save
    End Sub

Answer 1

您可以将日期比较替换为：

worksheetfunction.NetworkDays(varDate1,varDate2)

实际上使用VBA版本的工作表函数。如果您需要