Find a date inside range with for loop-CodePudding

I think is an easy one (not for a excel VBA dummy like me...) I'm trying to find a date (hour format) (writen in another cell C5) inside a range of dates. The code is the following one, but I receive nothing as an output, and I do not understand why... Thank you in advance for your help...

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

CodePudding user response：

If you indent your code properly, you can see that your Exit For will be executed after the first cell is checked every time.

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

CodePudding user response：

Handling Dates in 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