Using TRUNC of FIX function in to extract date part

[nhandal]

Hello,

I have a sheet that includes a list of datetime values in column B, I am using this code to loop through all rows to replace the number in each cell with only the date part of the number using the FIX function, but when I run the code VB returns an error "Object doesn't support this property or method. I can't figure out what is the problem. I appreciate someone's help.

Sub ConvertDateValues()

    Dim lRow As Long
    Dim lLastRow As Long
    Dim vCellDateNumber As Variant
        
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    
    For lRow = lLastRow To 2 Step -1
            
        vCellDateNumber = Cells(lRow, 2).Value
        Range(Cells(lRow, 2)).Value = Application.WorksheetFunction.Fix(vCellDateNumber)

    Next lRow

End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[nhandal]

Another option is

Sub nhandal()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",int(@))", "@", .Address))
   End With
End Sub

After running this code, I have a problem with another sub that I use to delete rows that contain dates before today. My code used to work but after running your code and truncating the date numbers I get a "type mismatch" error. Here is the code I used.

    For iRow = LastRow To 2 step -1   
        
        If Cells(iRow, 2).Value <= Now - 1 Then
           Cells(iRow, 2).EntireRow.Delete
        End If
        
    Next IRow

 

[Joe4]

Sounds like you have a non-numeric value somewhere.
When you get that error, if you hover over the "iRow" variable, what does it show for the value of that variable?
And then what is the exact value in column B of that particular row.

 

[nhandal]

Without having access to your data, I really cannot say. It may be picking up something you aren't expecting.

BTW, if all the numbers are numbers, why have you declared the variable as Variant?

Dim vCellDateNumber As Variant

That seems to suggest that ANYTHING is allowed, and not just numbers.

I declared it as variant because it is not integer and it is not long.

Here is how the data looks like:

45349.93​

45349.94​

45349.93​

45349.94​

45349.88​

45349.94​

45349.92​

45349.94​

45349.92​

 

[Joe4]

I declared it as variant because it is not integer and it is not long.

You can use "Single" or "Double" to include large numbers and decimals.
Variant will accept ANYTHING, including errors! So I would NOT recommend using that one, unless you plan on adding some error handling!

Please see my last post, and answer the two questions I asked there. That should show you exactly what is causing the code to fail.

 

[nhandal]

You can use "Single" or "Double" to include large numbers and decimals.
Variant will accept ANYTHING, including errors! So I would NOT recommend using that one, unless you plan on adding some error handling!

Please see my last post, and answer the two questions I asked there. That should show you exactly what is causing the code to fail.

You are right, there was one raw that had a non numeric value, now the code works perfectly.

Thank you for your help, much appreciated.