Date Format Frustrations

[scrawny]

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("B469471").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

 

[scrawny]

Oops...

Sorry if code is misleading - should be:

Set myRange = IMPORTED_DATA.Range("B469471")
Application.ReplaceFormat.NumberFormat = "d/mm/yyyy;@"
myRange.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_
ReplaceFormat:=True

 

[Roger Govier]

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

 

[scrawny]

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 B469471 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