Edit Cell Contents using SendKeys?

[jgscanlon]

Good morning -

I have a column of data containing dates that are entered as mm-ddd-yy. I have attempted to convert the format to mm/dd/yyy, but it appears the cell contents are being treated as text, and won't convert properly because Excel doesn't know if the year should be 19XX or 20XX.

I've been trying to use Application.SendKeys to edit the cell contents in my range of cells, but SendKeys appears to be hitting the VB editor, rather than my worksheet.

Does anyone have suggestions for editing the dates as mentioned above? Here's the code I've been using.:

    For Each Cell In Range("F2:F4").Cells
        Application.SendKeys ("{F2}{LEFT}{LEFT}20{ENTER}")
    Next Cell

Thank you!

 

[RoryA]

If they should all add "20", then you can use:

For Each Cell In Range("F2:F4").Cells
        cell.value = left(cell.value, 8) & "20" & right(cell.value, ")
    Next Cell

 

[Peter_SSs]

I'm thinking you have a lot mare than F2:F4 or you wouldn't be looking for a macro, so if the dates are actually text you could try this to 'fix' them all at once. I'm also assuming they all need a "20" inserted in them.

With Range("F2", Range("F" & Rows.Count).End(xlUp))
  .Value = Evaluate(Replace("if(len(#),left(#,7)&20&right(#,2),"""")", "#", .Address))
End With

Edit: .. and if you want to turn them from text dates into 'real' dates, add in this line

With Range("F2", Range("F" & Rows.Count).End(xlUp))
  .NumberFormat = "dd-mmm-yyyy"
  .Value = Evaluate(Replace("if(len(#),left(#,7)&20&right(#,2),"""")", "#", .Address))
End With

Further edit: Still assuming text values, you could do this manually fairly quickly too:
1. Select the column
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> remove any delimiters -> Next -> select Date: DMY -> Finish
3. Now format the column with the Date format you wish ("dd-mmm-yyyy")

 

[jgscanlon]

I'm thinking you have a lot mare than F2:F4 or you wouldn't be looking for a macro, so if the dates are actually text you could try this to 'fix' them all at once. I'm also assuming they all need a "20" inserted in them.

With Range("F2", Range("F" & Rows.Count).End(xlUp))
  .Value = Evaluate(Replace("if(len(#),left(#,7)&20&right(#,2),"""")", "#", .Address))
End With

Edit: .. and if you want to turn them from text dates into 'real' dates, add in this line

With Range("F2", Range("F" & Rows.Count).End(xlUp))
  .NumberFormat = "dd-mmm-yyyy"
  .Value = Evaluate(Replace("if(len(#),left(#,7)&20&right(#,2),"""")", "#", .Address))
End With

Thank you both for your responses! @Peter_SSs, you're right - I have a dynamic number of rows to work with. I just used 3 for my testing.

In the end I've gone with @Peter_SSs suggestion - it works perfectly! Thank you very much!

 

[Peter_SSs]

I was a bit slow, but I did a further edit you may have missed too.

 

[jgscanlon]

I did get that edit - very helpful! Thank you again.

 

[Peter_SSs]

I did get that edit - very helpful! Thank you again.

Cheers.