vba .NumberFormat date problem

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

The following code should set all dates in the selected
area to the new format. But it does not.

FYI: The paste values is to get rid of all formulas in the
target area.
---------------
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'
Range("A12:A100").Select
Selection.NumberFormat = "m/d/yy"
---------------

Using cell format within Excel does change the format.

What am I doing wrong?

Thanks,
Fred
 
Any chance that your "dates" aren't really dates. Maybe they're text
masquarading as dates.

Try retyping the date and see if that works.

If it does work, maybe just selecting that range and
edit|replace
/ (slash)
to
/ (slash)

will make excel reevaluate the entries.

If it works, you could even drop that mass change into your macro.

======
If this wasn't the problem, did you mean that the dates changed format, but not
to m/d/yy?

If this is the problem, then excel uses m/d/yy to represent the short date found
in Windows Regional settings|date tab.

I think that xl2002 is more honest with the way it deals with dates. (I think
it's new with xl2002, but maybe it's in xl2k, too.)

When I do Format|cells|Number Tab and click on the date category, I get a
message in the dialog:

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.

Maybe you could use a different (one that windows doesn't "own") format.
 
Back
Top