changing date format by macro in excel2000

[Guest]

i want to build a macro to change the format of the current workbook
from dd/mm/yyyy to dd/mm/yy
in excel2000
win XP

thank you
rozent

 

[Norman Jones]

Hi Rozent,

Try:
'=============>>
Sub ATest()
Dim rng As Range
Dim rCell As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<======= CHANGE

For Each SH In WB.Worksheets
Set rng = SH.UsedRange
For Each rCell In rng.Cells
If rCell.NumberFormat Like "*/*/yyyy" Then
rCell.NumberFormat = "dd/mm/yy"
End If
Next rCell
Next SH

End Sub
'<<=============

 

[Guest]

thank you norman, it helped.
is ther a shorter way?
can a macro change the definision in the control panel?

rozent

 

[Norman Jones]

Hi Rozent,

can a macro change the definision in the control panel?

I am not aware of a means to effect a global change with a custom format.

is ther a shorter way?

If you refer to the length of the code, I really do not believe that this
should be a consideration.

If you refer to macro execution time,
you could wrap the code thus:

Application.ScreenUpdating = False
'code
application.ScreenUpdating = True.

If you were happy to limit processing to existing date values, as opposed to
custom formatted cells which might (or might not) contain values, then
increased efficiency might ensue by changing:

to:

Set rng = SH.UsedRange.SpecialCells _
(xlCellTypeConstants, xlNumbers)

For date values entered as constants.

If, with the preceding limitation to existing date values, such dates were
returned from formulae, then you could use a range assignment like:

Set rng = SH.UsedRange.SpecialCells _
(xlCellTypeFormulas, xlNumbers)

If the dates of interest potentially result from either constants or
formulae, you would need to loop through each of the SpecialCells ranges or
form a union. This would increase code length but might wll improve
execution time

As written, the suggested code changes the custom format irrespective of
whether the formatted cells are currently populated.

 

[Guest]

hi norman
if i want to limit the macro only to the current worksheet
what is the macro

rozent

 

[Norman Jones]

Hi Rozent,

hi norman
if i want to limit the macro only to the current worksheet
what is the macro

Try:
'============>>
Sub ATest1A()
Dim rng As Range
Dim rCell As Range
Dim SH As Worksheet

Set SH = ActiveSheet

Set rng = SH.UsedRange
For Each rCell In rng.Cells
If rCell.NumberFormat Like "*/*/yyyy" Then
rCell.NumberFormat = "dd/mm/yy"
End If
Next rCell

End Sub
'<<=============