Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me finish this simple macro
Hello, Everyone!
I am trying to convert text dates to real dates. I need to find all yellow cells in range B4:B100, strip off the offending text (i.e. Sat. or Thurs), and use DateValue to turn the rest (which looks like 1/05/2007, but sometimes 1/05/07) into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place. I've spent days trying to write this macro. I've had excellent help from people on here, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below. Sub switchDate() Dim MyDate As String For Each cell In ActiveSheet.Range("B4:B100") If cell.Interior.ColorIndext < xlNone Then cell.Value = MyDate.Value MyDate = Trim(MyDate) If InStr(MyDate, " ") 0 Then MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) End If MyDate = DateValue(MyDate) End If Next End Sub This doesn't even begin to work and I can't begin to guess why. Could somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence? I thank you greatly, and have a terrific day. Arlen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me finish this simple macro
I haven't tried it yet but if you turn on the macrro recorder, then format
the string date to a real date format, manaully, I can almost guarantee that your code will be recorded. ML "Arlen" wrote: Hello, Everyone! I am trying to convert text dates to real dates. I need to find all yellow cells in range B4:B100, strip off the offending text (i.e. Sat. or Thurs), and use DateValue to turn the rest (which looks like 1/05/2007, but sometimes 1/05/07) into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place. I've spent days trying to write this macro. I've had excellent help from people on here, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below. Sub switchDate() Dim MyDate As String For Each cell In ActiveSheet.Range("B4:B100") If cell.Interior.ColorIndext < xlNone Then cell.Value = MyDate.Value MyDate = Trim(MyDate) If InStr(MyDate, " ") 0 Then MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) End If MyDate = DateValue(MyDate) End If Next End Sub This doesn't even begin to work and I can't begin to guess why. Could somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence? I thank you greatly, and have a terrific day. Arlen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me finish this simple macro
Ok, I just tried that.
This is what I got Range("Cell").Select 'or Activecell.Select Selection.NumberFormat = "m/d/yy;@" Is that what you are looking for? ML "Arlen" wrote: Hello, Everyone! I am trying to convert text dates to real dates. I need to find all yellow cells in range B4:B100, strip off the offending text (i.e. Sat. or Thurs), and use DateValue to turn the rest (which looks like 1/05/2007, but sometimes 1/05/07) into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place. I've spent days trying to write this macro. I've had excellent help from people on here, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below. Sub switchDate() Dim MyDate As String For Each cell In ActiveSheet.Range("B4:B100") If cell.Interior.ColorIndext < xlNone Then cell.Value = MyDate.Value MyDate = Trim(MyDate) If InStr(MyDate, " ") 0 Then MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) End If MyDate = DateValue(MyDate) End If Next End Sub This doesn't even begin to work and I can't begin to guess why. Could somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence? I thank you greatly, and have a terrific day. Arlen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help me finish this simple macro
Hi Arlen
If you don't mind me asking; why did your date format start out as a String? Are you retreiving a date from a user? ML "ML0940" wrote: Ok, I just tried that. This is what I got Range("Cell").Select 'or Activecell.Select Selection.NumberFormat = "m/d/yy;@" Is that what you are looking for? ML "Arlen" wrote: Hello, Everyone! I am trying to convert text dates to real dates. I need to find all yellow cells in range B4:B100, strip off the offending text (i.e. Sat. or Thurs), and use DateValue to turn the rest (which looks like 1/05/2007, but sometimes 1/05/07) into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place. I've spent days trying to write this macro. I've had excellent help from people on here, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below. Sub switchDate() Dim MyDate As String For Each cell In ActiveSheet.Range("B4:B100") If cell.Interior.ColorIndext < xlNone Then cell.Value = MyDate.Value MyDate = Trim(MyDate) If InStr(MyDate, " ") 0 Then MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) End If MyDate = DateValue(MyDate) End If Next End Sub This doesn't even begin to work and I can't begin to guess why. Could somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence? I thank you greatly, and have a terrific day. Arlen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro start and finish date from rows | Excel Worksheet Functions | |||
How to Create Sound Alert and Email Alert when Macro is Finish | Excel Programming | |||
How to Create Sound Alert and Email Alert when Macro is Finish | Excel Programming | |||
One more and I'll finish this complicated macro | Excel Programming | |||
Need help to finish function.... | Excel Programming |