This code is trying to delete all times from cells so just show`s Date

[Eric Penfold]

This code say`s runtime error 13??

Sub FormatCells()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim LRow   As Long
    Dim Rng    As Range
    Dim Cell   As Range
    Dim i      As Long

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet")
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    With ws
        Set Rng = ws.Range("F2:F" & LRow)
    End With
        
        For i = 2 To LRow
            With Range("F" & i)
                .NumberFormat = "dd/mm/yyyy"
                .Value = DateValue(.Value)
            End With
        Next i
        
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[Eric Penfold]

Sorry just realized row 2 was hidden so here are the true results

=LEN(F3)=16 then =CODE(MID(F3,11,1))=48

 

[Joe4]

So, if there is nothing in row 2, and your data actually starts on row 3, you need to change this line:

        For i = 2 To LRow

to this:

        For i = 3 To LRow

 

[Eric Penfold]

Sorry yes it now says Expected array which i added. And when i run it it deletes the cell values.

 

[Joe4]

Sorry yes it now says Expected array

What does? When you try running the code?
Once again, is there a debug button, and if you hit it, what line of code does it highlight?

I think it would be much more efficient if you could show us exactly what your sheet looks like.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Eric Penfold]

The cell i am trying to change to just the date. The code clears the value. No line is highlighted as a error.

 

[Joe4]

It sounds like the values in column F may not actually hard-coded in, but may be the results of formulas.
Is that true?

 

[Eric Penfold]

Their is no formula in Excel for the date.

 

[Joe4]

Then I don't understand what you are telling me with that array comment.
I don't know that there is going to be much more I can do for you without having access to the workbook.