Upper Case and date format issue

[Andy Tallent]

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

--
Andy Tallent

[widemonk]

I cant duplicate you error precisely, but it has something to do with the DD/MM/YY format against hh:mm:ss.ss

while dd and yy dont really mind which case they're in... MM does.

MM = month, mm = minutes.

It might also be worth pointing out, HH returns 24-hour clock while hh does the 12-hour clock.

[Duke Carey]

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




"Andy Tallent" wrote:

> 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
>
> --
> Andy Tallent

[Andy Tallent]

Hi Duke,

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

Regards

Andy

"Duke Carey" wrote:

> 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
>
>
>
>
> "Andy Tallent" wrote:
>
> > 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
> >
> > --
> > Andy Tallent

[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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Andy Tallent" <[email protected]> wrote in message news:[email protected]...
> Hi Duke,
>
> This is working perfectly now. Many thanks for your help.
>
> Regards
>
> Andy
>
> "Duke Carey" wrote:
>
> > 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
> >
> >
> >
> >
> > "Andy Tallent" wrote:
> >
> > > 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
> > >
> > > --
> > > Andy Tallent