VBA - Using NumberFormat on a range but only on cells containing Dates

[30136353]

Hi Guys,
I have the below code which is working great, but the part of the code which changes the number format in Column D to "d/m", I need this to only work on cells containing dates. Within Column D I have address numbers, some are automatically held as dates, but some may be single numbers like "6". After I format using the above 6 would show as 6/1.... I understand dates are essnaitally large numbers, so if there is way to only Numberformat cells greater tha

    Sub AddressFix()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

 
    Columns("A:M").HorizontalAlignment = xlCenter
    Columns("D:D").Replace What:="0", Replacement:="", LookAt:=xlPart
    Columns("D:D").Replace What:=" ", Replacement:="", LookAt:=xlPart

    Dim c  As Range
    For Each c In Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    If c.Value = "" Then c.Value = c.Offset(, -1).Value
    Next
    
    Columns("A").ColumnWidth = 17
    Range("D2:D200").NumberFormat = "d/m"
     Range("C2:C200").ClearContents
     End Sub

n say 2000, then this would work. But I'm not sure hwo to write this within the code...


Thanks,

 

[6StringJazzer]

How is your date data stored and formatter to start with? If it is entered as dates and you just want to change the formatting this should work. You have to use a loop, which will take a little bit longer than what you have now.

Excel dates are not "essentially large numbers." The underlying value is the number of days since 1/1/1900. Today's date, for example, is 43,819. That is not that big a number. But Excel does distinguish whether a value is a number that is in that range vs. a date.

   Sub AddressFix()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Columns("A:M").HorizontalAlignment = xlCenter
    Columns("D:D").Replace What:="0", Replacement:="", LookAt:=xlPart
    Columns("D:D").Replace What:=" ", Replacement:="", LookAt:=xlPart

    Dim c  As Range
    For Each c In Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    If c.Value = "" Then c.Value = c.Offset(, -1).Value
    Next
   
    Columns("A").ColumnWidth = 17
    For Each c In Range("D2:D200")
      If IsDate(c) Then
         c.NumberFormat = "d/m"
      End If
   Next c
     Range("C2:C200").ClearContents
     End Sub

 

[30136353]

How is your date data stored and formatter to start with? If it is entered as dates and you just want to change the formatting this should work. You have to use a loop, which will take a little bit longer than what you have now.

Excel dates are not "essentially large numbers." The underlying value is the number of days since 1/1/1900. Today's date, for example, is 43,819. That is not that big a number. But Excel does distinguish whether a value is a number that is in that range vs. a date.

   Sub AddressFix()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    Columns("A:M").HorizontalAlignment = xlCenter
    Columns("D:D").Replace What:="0", Replacement:="", LookAt:=xlPart
    Columns("D:D").Replace What:=" ", Replacement:="", LookAt:=xlPart

    Dim c  As Range
    For Each c In Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    If c.Value = "" Then c.Value = c.Offset(, -1).Value
    Next
  
    Columns("A").ColumnWidth = 17
    For Each c In Range("D2:D200")
      If IsDate(c) Then
         c.NumberFormat = "d/m"
      End If
   Next c
     Range("C2:C200").ClearContents
     End Sub

That worked a treat, thanks for the help.