Identification and Reformatting of Dates

[reberryjr]

I'm in need of some assistance with some data calculations. I ingest data from 24 files. Most of the files have a date that I bring over into column D, but they aren't all formatted the same. Examples of the ingested formatting are:
• Serial Number
• YYYY-MM
• YYYY.MM.DD
• Blank
I'm trying to interrogate the value in column D, and populate column E with the date formatted as "MMM-YY". I'm sure there's a more efficient route than the one I was going down, but here's what I've tried unsuccessfully. I only got about 90 minutes of sleep last night, so maybe that's why I'm extra dumb today.

Sub FormatTime()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mD As Worksheet
Dim c As Range, rng As Range
Dim mDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

Set rng = mD.Range("D2", mD.Range("D" & mD.Rows.Count).End(xlUp))

mDLR = mD.Range("C" & Rows.Count).End(xlUp).Row

For Each c In rng
    If c.Value = "" Then
        c.Offset(, 1).Value = "N/A"
    Else
        c.Offset(, 1).Value = "=TEXT(RC[-1],""MMM-YY"")"
    End If
Next c

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[reberryjr]

do you have VBA debugging capability? If you do set a break-point in the top of your loop and step thru the code. Find where the error is occuring (at what line of code).

I do. It's here.

    With mD.Range("E" & r)
        .Value = dt 'Application defined or Object defined error
        .NumberFormat = "MMM-YY"
    End With

 

[Bosquedeguate]

A

Here's what I'm using. I'm getting an application defined or object defined error (location is commented out)

Sub DateCalcs1()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mD As Worksheet
Dim mDLR As Long
Dim rng As Range
Dim r As Integer
Dim dsStr As String
Dim dt As Date

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

'For r = 2 To mDLR
'    dtstr = Replace(mD.Range("D" & r), ".", "-")
'
'    dt = DateValue(dtstr)
'
'    With mD.Range("E" & r)
'        .Value = dt
'        .NumberFormat = "MMM-YY"
'    End With
'Next r

For r = 2 To mDLR
    If VarType(mD.Range("D" & r)) = vbDate Then
        dt = mD.Range("D" & r)
    Else
        dtstr = Replace(mD.Range("D" & r), ".", "-")
        dt = DateValue(dtstr)
    End If
   
    With mD.Range("E" & r)
        .Value = dt 'Application defined or Object defined error
        .NumberFormat = "MMM-YY"
    End With
Next r

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Bosquedeguate]

Here's are a couple of things to try ....
With mD.Range("E" & r)
.Value = dt 'Application defined or Object defined error
.NumberFormat = "MMM-YY"
End With

In the above code insert
MsgBox dt 'make sure we have a valid number here

Also try replacing the two lines inside the With, End With with
mdRange("E" & r) = dt
mdRange("E" & r).NumberFormat = "MMM-YY"

And comment out the With and End With lines

What vesion of Excel are you using? A desktop version or Office 365?

 

[reberryjr]

Still the same error in the same spot. I'm using 365

 

[Bosquedeguate]

OK ... I did some searching on the internet and found several articles on Error 1004 - Application defined or Object defined error.
It suggested defining a Range prior to trying to use. So, give this a try when you can ...

Replace the code where you're getting the error

    With mD.Range("E" & r)
        .Value = dt 'Application defined or Object defined error
        .NumberFormat = "MMM-YY"
    End With

With the following code:

    Set rng = mD.Range("E" & r)
    With rng
        .Value = dt 'Application defined or Object defined error
        .NumberFormat = "MMM-YY"
    End With

Please give this a shot and let me know what happens