使用 NETWORKDAYS 的 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26466659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA using NETWORKDAYS
提问by WKI
I have created a sub that checks if changes has happened in column Fand writes the timestamp to a corresponding cell in column G. How do i edit this sub to return the network days in column Hby finding the difference between the timestamp in column H and Cell containing a week commencing date A1? Without VBA, the formula is =ABS(NETWORKDAYS(A1, B1) - SIGN(NETWORKDAYS(A1, H1)). Below is my code so far. Any help?
我创建了一个 sub 来检查 column 中是否发生了更改F，并将时间戳写入column中的相应单元格G。如何H通过查找 H 列中的时间戳和包含一周开始日期的 Cell 之间的差异来编辑此子项以返回列中的网络天数A1？没有VBA，公式是=ABS(NETWORKDAYS(A1, B1) - SIGN(NETWORKDAYS(A1, H1))。以下是我到目前为止的代码。有什么帮助吗？
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Application.EnableEvents = False
With Target
'check if change happened in column F
If .Column = 6 Then
'check if changed value is X
If Not IsEmpty(c) Then
'add datestamp if it is
Cells(.Row, 7).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
Cells(.Row, 8).Value = ABS(NETWORKDAYS(G5,H2)-SIGN(NETWORKDAYS(G5,H2)
Else
'clear datestamp and Column H if not
Cells(.Row, 7).Value = ""
Cells(.Row, 8).Value = ""
End If
End If
End With
Application.EnableEvents = True
End Sub
'
'
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Dim d1 As Date, d2 As Date, wf As WorksheetFunction
Dim N As Long
Set wf = Application.WorksheetFunction
Application.EnableEvents = False
With Target
'check if change happened in column F
If .Column = 2 Then
'check if changed value is X
If Not IsEmpty(c) Then
'add datestamp if it is
d1 = Cells.Range("A1")
d2 = Cells.Range("B1:B2")
N = wf.NetworkDays(d1, d2)
Cells(.Row, 4).Value = N
Else
'clear datestamp and Colunm H if not
Cells(.Row, 4).Value = ""
End If
End If
End With
Application.EnableEvents = True
End Sub
回答by Gary's Student
Here is how to use Networkdays()in VBA
以下是如何在VBA 中使用Networkdays()
Sub dural()
Dim d1 As Date, d2 As Date, wf As WorksheetFunction
Dim N As Long
Set wf = Application.WorksheetFunction
d1 = DateValue("1/1/2014")
d2 = DateValue("12/31/2014")
N = wf.NetworkDays(d1, d2)
MsgBox N
End Sub
回答by n8.
Once I cleaned up your indentations it's pretty clear that you have an "End Sub" in the middle of an "If" statement in your "Worksheet_Change" sub. Formatting helps you catch this kind of thing.
一旦我清理了你的缩进，很明显你在“Worksheet_Change”子中的“If”语句中间有一个“End Sub”。格式化可以帮助您捕捉这种情况。
