none
Run time error 13 Type mismatch in my code? RRS feed

  • Question

  • I have dates in different formats in a column: For example 1-Mar-19 and 01-Mar-2019. I am trying to convert into the format 03.01.2019. This is my code:

    Sub ConvertToDate()
    
      Dim r As Range
      Dim setdate As Range
    
      Set setdate = Range(Cells(3, 3), Cells(3, 3).End(xlDown))
    
      With setdate
        .NumberFormat = "dd.mm.yyyy" 
        .Value = .Value
        .NumberFormat = "d.mm.yy"
      End With
    
      For Each r In setdate
          r.Value = CDate(r.Value)
      Next r
    
    End Sub

    The error is shown in the line:

    r.Value = CDate(r.Value)


    sajjad haider


    • Edited by Sajjad05 Wednesday, March 6, 2019 12:38 PM
    Wednesday, March 6, 2019 9:26 AM

All replies

  • Try the following code.

    Sub ConvertToDate()
       
        Dim r As Range
        Dim setDate As Range
       
        Set setDate = Range(Cells(3, 3), Cells(3, 3).End(xlDown))
       
        With setDate
            '.NumberFormat = "dd.mm.yyyy"       'don't think you need this
            '.Value = .Value                    'don't think you need this
            .NumberFormat = "d.mm.yy"
        End With
       
        For Each r In setDate
            If IsDate(r.Value) Then
                r.Value = CDate(r.Value)
            Else
                'Following to identify any values that are not valid for date conversion
                MsgBox r.Address(0, 0) & " Value: " & r.Value & " cannot be converted to a date"
            End If
        Next r

    End Sub


    Regards, OssieMac

    Tuesday, April 2, 2019 4:35 AM