用for循环在范围内查找日期

Question

我认为这很容易(对于像我这样的excel VBA虚拟人来说.)我试图在一个日期范围内找到一个日期(小时格式)(在另一个单元格C5中写)。代码如下，但我没有收到任何输出，我不明白为什么.提前谢谢你的帮助..。

Sub recorrer()

Dim rng As Range, cell As Range
Sheets("General").Activate

Set rng = Range("A22", Range("A22").End(xlDown))

For Each cell In rng
    If DateDiff("h", Sheets("Hoja1").Range("C5").Value, cell.Value) < 0 Then
    cell = Sheets("Hoja1").Range("C2").Value
    End If
Exit For

Next

End Sub

Answer 1

在VBA中处理日期

Sub Recorrer()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    ' Source (read from)
    Dim sws As Worksheet: Set sws = wb.Worksheets("Hoja1")
    Dim sCompareValue As Variant: sCompareValue = sws.Range("C5").Value
    Dim sReplaceValue As Variant: sReplaceValue = sws.Range("C2").Value
    
    ' Destination (write to)
    Dim dws As Worksheet: Set dws = wb.Worksheets("General")
    Dim drg As Range
    Set drg = dws.Range("A22", dws.Cells(dws.Rows.Count, "A").End(xlUp))
    
    If Not IsDate(sCompareValue) Then Exit Sub
    
    Dim dCell As Range
    Dim dValue As Variant
    
    For Each dCell In drg.Cells
        dValue = dCell.Value
        If IsDate(dValue) Then ' is a date
            If DateDiff("h", sCompareValue, dValue) < 0 Then ' criteria met
                dCell.Value = sReplaceValue
                ' If you want to do it only to the first cell
                ' that meets the criteria, you will use:
                'Exit For
            'Else ' criteria not met
            End If
        'Else ' is not a date
        End If
    Next

End Sub

Answer 2

如果您正确地缩进您的代码，您可以看到每次检查第一个单元后都会执行您的Exit For。

Sub recorrer()
    Dim rng As Range, cell As Range
    Sheets("General").Activate
    
    Set rng = Range("A22", Range("A22").End(xlDown))
    
    For Each cell In rng
        If DateDiff("h", Sheets("Hoja1").Range("C5").Value, cell.Value) < 0 Then
            cell = Sheets("Hoja1").Range("C2").Value
        End If
        Exit For
    Next
End Sub