Coverting YYYYMMDD to dd/mm/yyyy ignoring cells with 0 value

[YasSheikh]

Hi all

VBA novice here. I am trying to write a VBA code to convert YYYYMMDD to dd/mm/yyyy format however my range has some 0 values. I have tried writing the below code based on other threads on the board however I keep getting an error "Type mismatch".

I am essentially trying to 'hide' the 0 values and converting all others to dd/mm/yyyy date format. Any help would be greatly appreciated!

Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    If c.Value = 0 Then c.NumberFormat = "#;#;" Else
        c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
        c.NumberFormat = "dd/mm/yyyy"
    Next
    Application.ScreenUpdating = False

Cheers!

 

[RoryA]

That is not the correct structure for your If block. You need:

    For Each c In Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
      If c.Value = 0 Then
         c.NumberFormat = "#;#;"
      Else
         c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
         c.NumberFormat = "dd/mm/yyyy"
      End If
    Next

 

[YasSheikh]

@RoryA - thank you very much! works perfectly.

 

[RoryA]

Glad we could help.