Upper Case and date format issue

[Guest]

I'm using some code from Chip Pearsons site to convert a range to Upper Case.

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

I've noticed that whenever there is a date in the range it "flips" the
format. Thus 01/02/2004 is converted to 02/01/2004. Is there a way around
this?

Many Thanks

Andy

 

[Guest]

I'm not seeing that behavior on my XL2000 instance, but if you want to, you
can modify Chip's code by changing this line

If Rng.HasFormula = False Then

to

If Not Rng.HasFormula And Not IsDate(rng) Then

 

[Guest]

Hi Duke,

This is working perfectly now. Many thanks for your help.

Regards

Andy

 

[David McRitchie]

If you went back to the original formula without eliminating dates
what happens if you use .formula instead of .value

In reality I would limit the scope to text constants
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

and if you selected an *entire* column you might notice
a tremendous difference. My system is 600 MHz that would
take 6 minutes on my machine with lots of paging with only
128MB RAM. On a 3 GHz machine you would have to
choose 5 columns, but with a big machine you probably
would not be paging so might run 1000 times faster so
it might be barely noticeable to someone on a big machine.
But you might try the comparison yourself.

With a macro as described on my page, you should be
able to convert 200 cells even though selecting an entire
column faster than it took to select the macro.