excel VBA，检查日期列表属于哪一周

Question

这里的VBA用户非常缺乏经验。。。

我有一个固定长度范围内的日期列表和另一个固定范围内的周开始日期列表。我想查一下每个日期的最后一周。

我刚开始使用代码，但现在在为..嵌套两个代码时遇到了问题。。每个循环。我想如果和下一个陈述的位置不对，我就完了。我得到错误"如果没有块如果就结束"。我还想知道，是否有必要用两个来表示。。每个循环执行此操作。

我刚刚输入了打印"是"one_answers"否"行，以检查我试图做的事情是否有效——如果不起作用，则用下一阶段的代码替换打印行。

如有任何帮助，我们将不胜感激！

感谢

Dim due As Range
Dim dueDate As Date
Dim datecell As Range
Dim cashflow As Range
Dim cashcell As Range
Dim startweek As Date
Dim endweek As Date
Set due = Range("H21:H36")
Set cashflow = Range("N19:AT19")
For Each cashcell In cashflow
If IsDate(cashcell.Value) Then
startweek = cashcell.Value
endweek = cashcell.Offset(0, 2).Value
For Each datecell In due
If datecell >= startweek And datecell < endweek Then
Debug.Print "YES"
Else
Debug.Print "NO"
End If
End If
Next datecell
Next cashcell
End Sub

Answer 1

随意匹配If和For循环的缩进开始和结束：

Dim due As Range
Dim dueDate As Date
Dim datecell As Range
Dim cashflow As Range
Dim cashcell As Range
Dim startweek As Date
Dim endweek As Date
Set due = Range("H21:H36")
Set cashflow = Range("N19:AT19")
For Each cashcell In cashflow
If IsDate(cashcell.Value) Then
startweek = cashcell.Value
endweek = cashcell.Offset(0, 2).Value
For Each datecell In due
If datecell >= startweek And datecell < endweek Then
Debug.Print "YES"
Else
Debug.Print "NO"
End If
End If    ' <-- this If statement starts OUTSIDE the for loop
Next datecell ' <-- This For loop starts INSIDE the If IsDate...
Next cashcell
End Sub

您的问题是不能在结束For Each datecell...子句之前结束If IsDate...子句。切换这两行，使Next datecell语句在End If之前运行，并且代码应该执行