replace all

[wibbers2000]

I have attached a small file which I have got a problem with

that data comes from a CSV file...

I delete a 3 rows and and one column.

I then move column C and insert before column A do a bit of autofitting and everything is ok

so far so good

This is where I get the problem.

Then I replace the '.' with a '/' so that the data should be treated as a date.

When I recorded the macro (about 5 times) it works perfect and gives me the result I require. However, if a copy the same data from another file and rerun the macro it doesnt work. Only some of the cells are changed.

If I manually select the column and change anything i.e replace '/' with '/' it works fine.

Can anyone help, as I lost count of the number of hours I have spent trying to resolve this problem.

Regards
Wibbers

[tpoynton]

Is the sheet protected? I dont know anything about protection, but when i go to tools | protection, it seems like protection is activated...it is odd that some of the cells will let you change their format and others will not...

[wibbers2000]

No there is no protection on the sheet.


and I agree it seams strange that some cells change but other dont, but believe me I have looked for a cause, but without results.

Regards

[Rembo]

Hello Wibbers,


.. However, if a copy the same data from another file and rerun the macro it doesnt work. Only some of the cells are changed.
If I manually select the column and change anything i.e replace '/' with '/' it works fine.

This has to do with the fact that data from a csv file is by default of type string (text). A date is not recognized as such. If you manually convert it to a date format Excel is kind enough to convert it to a date value for you, that is if Excel knows how to do that.

A second problem with this type of converting can exist in your local setting. Excel can interprete 1/4/05 as 1 April 2005 or as January 4th 2005. Which one is it? You will probably notice that the dates that are correctly converted by your macro all have month and daynumbers of 12 or lower. They are converted to dates alright, but not the correct ones ;-)

In VBA you can solve this by converting the text to a date before trying to format it. That way you'll have a little more control. Try the code below and see if that works for you. Note that I explicitly had to format the date to use a backslash (/). If you remove that line (.NumberFormat..) you'll get the date in the system default format.

A final note; in VBA you don't have to select a range prior to changing it's properties. Rather then using Range("A1").Select and Selection.Value = "hello" you can simply write Range("A1").Value = "hello".

Rembo

Sub changedate()
'
' changedate Macro
' Macro recorded 06/12/2005 by pwibberly
' Modified by Rembo 6/12/2005

' Added two variables
Dim rCell As Range
Dim lLastrow As Long

' Added this line to prevent screen flickering and speeding up the routine
Application.ScreenUpdating = False

Rows("1:3").Delete Shift:=xlUp
Columns("A:A").Delete Shift:=xlToLeft
Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight
Columns("A:A").EntireColumn.AutoFit
Columns("I:I").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Added this routine to convert strings to dates
lLastrow = Range("I65536").End(xlUp).Row
For Each rCell In Range("I2:I" & lLastrow)
If rCell.Value <> "" Then
rCell.Value = CDate(rCell.Value)
rCell.NumberFormat = "dd\/mm\/yyyy"
End If
Next rCell

Columns("I:I").ColumnWidth = 15.57
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select

' Turn screen updating back on
Application.ScreenUpdating = True
End Sub

[wibbers2000]

Excellent Rembo,

Not only did you solve the problem, but gave me a good explanation and tided the code up as well.

Thanks for all your help, it is much appreciated.

Regards