CODEWITHSUNDEEP
vbaexceldatetimetype-conversion
Vedad
Vedad

Reputation: 223

VBA Convert Datetime to Hour in a Column

i wanted to Convert the Datetime values in one Column to just Hour Values. Now I tried it with a loop but I get the error saying that the Types are incompatible. Any Idea?

Dim l As Range
For Each l In Hour.Range("A1", "A" & lngLastRow)
If l.Value <> "" Then l.Value = Hour(l.Value)
Next

Upvotes: 1

Views: 712

Answers (1)

Vityata
Vityata

Reputation: 43575

Out of all the possible names in the world, did you really named your Worksheet Hour or am I wrong?

If this is the case, simply rename the worksheet, it would automatically fix everything and thus you would be happy. And never use names like Minute, Hour, Second, Integer for any variable or object.

This is a possible solution then:

Option Explicit

Public Sub Testing()

    Dim l               As Range
    Dim lngLastrow      As Long: lngLastrow = 6

    For Each l In ActiveSheet.Range("A1", "A" & lngLastrow)
        If l <> "" Then
            l.Offset(0, 1) = Format(l, "HH")
        End If
    Next

End Sub

Make sure to change the name here, not on the tab:

enter image description here

Upvotes: 1

Related Questions