Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Frustrations
G'Day,
Got a spreadsheet that imports data and unfortunately the date data comes in the format of "dd.mm.yyyy". So I use a replace method to change dots to slashes to get "dd/mm/yyyy". When I do this with the replace in the edit menu it works fine. When I record the macro that makes it all happen and place it in my VBA code it changes the date format around to "m/dd/yyyy". So I tried recording the macro again with the replace dialog box options and selecting the proper date format... once again the menu option works fine but the actual code doesn't (see below)... Range("B469:D471").Select Application.ReplaceFormat.NumberFormat = "d/mm/yyyy;@" myRange.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True The Application.ReplaceFormat... line returns the following error: Run-time error '-2147417848 (80010108)': Method 'NumberFormat' of object 'CellFormat' failed Anyone come across this before? -Scrawny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Frustrations
Oops...
Sorry if code is misleading - should be: Set myRange = IMPORTED_DATA.Range("B469:D471") Application.ReplaceFormat.NumberFormat = "d/mm/yyyy;@" myRange.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ ReplaceFormat:=True |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Frustrations
Hi
I can't answer why your code doesn't work, but try the following as an alternative. Sub convdate() Dim i As Long For i = 2 To 4 ' columns B to D Range(Cells(469, i), Cells(471, i)).TextToColumns _ Destination:=Range(Cells(469, i), Cells(471, i)), _ DataType:=xlDelimited, FieldInfo:=Array(1, 4) Next i End Sub It works fine for me converting dates like 9.4.7 to 09/04/2007. My Regional Setting is UK and I use dd/mm/yyyy -- Regards Roger Govier "scrawny" wrote in message ups.com... G'Day, Got a spreadsheet that imports data and unfortunately the date data comes in the format of "dd.mm.yyyy". So I use a replace method to change dots to slashes to get "dd/mm/yyyy". When I do this with the replace in the edit menu it works fine. When I record the macro that makes it all happen and place it in my VBA code it changes the date format around to "m/dd/yyyy". So I tried recording the macro again with the replace dialog box options and selecting the proper date format... once again the menu option works fine but the actual code doesn't (see below)... Range("B469:D471").Select Application.ReplaceFormat.NumberFormat = "d/mm/yyyy;@" myRange.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True The Application.ReplaceFormat... line returns the following error: Run-time error '-2147417848 (80010108)': Method 'NumberFormat' of object 'CellFormat' failed Anyone come across this before? -Scrawny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Frustrations
You Legend!
Because the importer sheet (the sheet getting the data) only needs Column B, C & D to have the format changed from something like 03.04.2007 - I just used the following code: (Because the sheet is so huge and it's running on a network I'm trying to stay away from using loops, ifs etc.) (Also, The B469:D471 above is variable with the changeRange and putRange addresses which is the actual code below...) Set matchRange = IMPORTED_DATA.Range(changeRange.Offset(0, 1).Address & ":" & putRange.Offset(0, 1).Address) matchRange.TextToColumns Destination:=matchRange, DataType:=xlDelimited, FieldInfo:=Array(1, 4) Set matchRange = matchRange.Offset(0, 1) matchRange.TextToColumns Destination:=matchRange, DataType:=xlDelimited, FieldInfo:=Array(1, 4) Set matchRange = matchRange.Offset(0, 1) matchRange.TextToColumns Destination:=matchRange, DataType:=xlDelimited, FieldInfo:=Array(1, 4) You know - this would never have happened if them bloomin Yanks didn't have their standards imprinted into Microsoft Excel... (Despite what regional settings you might have in place on your machine). Oh well. -Scrawny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IRR Calculation Frustrations | Excel Discussion (Misc queries) | |||
Concatenation frustrations.... | Excel Worksheet Functions | |||
irr, xirr, npv frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions | |||
DSUM frustrations | Excel Worksheet Functions |