如果空白不停止循环

Question

我需要您的帮助：

列J（完成日期）

列G（到期日）

列M（将显示：提早，按时或延迟）

• 当j空白时，我希望循环停止
• 它不在下面的代码中，J列中的所有单元格都已填充
• d值始终为0（我在M中按时获得全部时间）

注意：当我尝试过一个单元格时，它可以正常工作

Sub TIMESTATUS()
    Dim CompletionDate As Long
    Dim DueDate As Long
    Dim D As Boolean
    For Each C In Sheet1.Range("j:j")
        If C.Value = "" Then
            Exit For
        Else
            For Each g In Sheet1.Range("g:g")
                CompletionDate = C.Value
                DueDate = g.Value
                D = CompletionDate - DueDate
                If D > 0 Then
                    Range("m:m").Value = "Delay"
                ElseIf D < 0 Then
                    Range("m:m").Value = "Early"
                ElseIf D = 0 Then
                    Range("m:m").Value = "On Time"
                End If
            Next
        End If
    Next
End Sub

Answer 1

您可能会在这样的事情之后：

Sub TIMESTATUS()        
    Dim C As Range
    Dim strng As String
    With Sheet1 '<--| reference your sheet
        For Each C In .Range("J:J").SpecialCells(xlCellTypeConstants, xlNumbers) '<--| loop through its column J not empty cells with numbers only
            Select Case C.Value - .Cells(C.row, "G").Value '<--| check the difference between curent column "J" cell and its corresponding one i column "G" against following cases
                Case Is > 0 '<--| if difference >0
                    strng = "Delay"
                Case Is < 0 '<--| if difference <0
                    strng = "Early"
                Case Else '<--| esle (i.e. if difference =0)
                    strng = "On Time"
            End Select
            .Cells(C.row, "M").Value = strng '<--| write down the value
        Next
    End With
End Sub

Answer 2

在您的代码D中是布尔值。这意味着它具有两个值-True或False。因此，代码类似： If D > 0 Then ElseIf D < 0 Then ElseIf D = 0 Then是毫无意义的...您可以在此处阅读更多有关布尔值的信息：https://msdn.microsoft.com/en-us/library/wts3hb3.aspx