Inconsistency concerning Excels auto format function

[Doug Williams]

Hi guys,

I work with Excel to process and correct data files and I have developed a macro to reduce the work load in this area.

I have found that using macros don't always replicate the actions performed using the manual controls in Excel. Doing a 'find and replace' on "-" to "/" on a cell containing the date "12-06-09" will give the correct Australian date of "12/06/09" from the manual controls. When running the equivilent find a replace function in a Macro, Excel AutoFormats the data into US format "06/12/09".

A similar issue occurs when you use the save fuction in a Macro. US dates are applied. Saving a file manually leaves the dates in the correct Australian format.

Why does this happen?

Is there anyway to prevent the AutoFormat process from happing in the code?

Are there any options to setup the AutoFormat date to Australian?


Another related question. Is there anyway to prevent Auto Formatting when opening CSV files? Mobile phone numbers start with a "0" and excel thinks the cell should be a number. This is not the case and as a result it corrupts the file. I have used the import function of excel (I set columns to text manually in the import process) to combat this, but it is very time consuming.

 

[Ed in Aus]

well you could just use this.

Var = Format(Date, "DD/MM/YYYY")

but for find and replace it may be a bit different, may have to use an if statement.

as for the mobile number one you could format as text, or just enter ' before you start typing the number, another option is seperate the numbers so Excel treats different to a number i.e. 0404 04 04 04 or 0404 040 404

 

[Ed in Aus]

you could use this to replace th US formatted dates, just select the cells that need changing and run this. (Note try not to select the whole work sheet might take a while to run if you do)

Function IsADate(cel) As Boolean
    IsADate = IsDate(cel)
End Function
Sub test8()
For Each c In Selection
If IsADate(c) = True Then
c = Format(c, "dd/mm/yyyy")
End If
Next c
End Sub