troubleshooting Question

formatting data

Avatar of mcrmg
mcrmg asked on
VBA
14 Comments1 Solution154 ViewsLast Modified:
Hi,

With EE's help, I am able to change the format using vba. The code is following
https://www.experts-exchange.com/questions/29120436/formatting-data.html
Sub FormatDates()
Dim cell As Range
Dim lr As Long
Dim dtStr As String
Application.ScreenUpdating = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each cell In Range("A2:B" & lr)
    If cell <> "" And IsNumeric(cell.Value) And Evaluate(Len(cell.Text)) > 5 Then
        dtStr = cell.Value
        cell.Value = DateSerial(Left(dtStr, 4), Mid(dtStr, 5, 2), Right(dtStr, 2))
    End If
Next cell
Range("A2:B" & lr).NumberFormat = "mm/dd/yyyy"
Application.ScreenUpdating = True
End Sub

I found out there are some cells with strange data. See row 82. If I format this cell with NUMBER with no decimal. It will give me the date. This is my code, obviously, it is not working. I was wondering if EE could give me some directions. thanks

Sub FormatDates()
Dim cell As Range
Dim lr As Long
Dim dtStr As String
Application.ScreenUpdating = False

lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row



For Each cell In Range("A2:A" & lr)
    If cell <> "" And IsNumeric(cell.Value) And Evaluate(Len(cell.Text)) > 5 Then
        dtStr = cell.Value
        cell.Value = DateSerial(Left(dtStr, 4), Mid(dtStr, 5, 2), Right(dtStr, 2))
    Else
        Range("A" & lr).NumberFormat = "0"
        dtStr = cell.Value
        cell.Value = DateSerial(Left(dtStr, 4), Mid(dtStr, 5, 2), Right(dtStr, 2))
    End If
Next cell
Range("A2:A" & lr).NumberFormat = "mm/dd/yyyy"





Application.ScreenUpdating = True




End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros