Upper case

[Andy]

Hi,

With the following, I was able to change a multi-row
selection to proper case

dim myArray as variance

range("a1:a20").select
set myArray = selection
myArray = application.proper(myArray)

This does not work with UCase. Apart from using For Next
loop, is there a way similar to above code that works. I'm
looking for an alternative that works faster than For Next
loop

Thanks

 

[JE McGimpsey]

One way:

Dim rCell As Range
For Each rCell In Range("A1:A20")
rCell.Value = UCase(rCell.Text)
Next rCell

 

[Andy]

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

 

[Bob Phillips]

No, you have to iterate through the whole range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

One way:

Dim rCell As Range
For Each rCell In Range("A1:A20")
rCell.Value = UCase(rCell.Text)
Next rCell

 

[JE McGimpsey]

No, you'll have to loop. OTOH, For Each...Next is rather fast,
especially if you're only converting 20 cells.

 

[Andy]

Thanks to both you and Bob.
I used 20 cells in my example but actually my spreadsheet covers a few
thousand rows and it's growing each day. Anyway, I normally read this NG
while the For Each Loop is working, so thankfully it helps me gain Excel
knowledge.

No, you'll have to loop. OTOH, For Each...Next is rather fast,
especially if you're only converting 20 cells.

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

 

[Dave Peterson]

But interestingly, you can convert a range to proper in one fell swoop.

Selection.Value = Application.Proper(Selection.Value)

(well, I thought it was interesting!)

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

One way:

Dim rCell As Range
For Each rCell In Range("A1:A20")
rCell.Value = UCase(rCell.Text)
Next rCell

 

[Bob Phillips]

that's not interesting, it's perverse<vbg>. Discrimination even.

Bob

But interestingly, you can convert a range to proper in one fell swoop.

Selection.Value = Application.Proper(Selection.Value)

(well, I thought it was interesting!)

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

One way:

Dim rCell As Range
For Each rCell In Range("A1:A20")
rCell.Value = UCase(rCell.Text)
Next rCell

Hi,

With the following, I was able to change a multi-row
selection to proper case

dim myArray as variance

range("a1:a20").select
set myArray = selection
myArray = application.proper(myArray)

This does not work with UCase. Apart from using For Next
loop, is there a way similar to above code that works. I'm
looking for an alternative that works faster than For Next
loop

Thanks

 

[JE McGimpsey]

I'd guess that if you could use

Application.Upper()

it would work, too.

VBA methods aren't very good at array ops.

 

[Andy]

Thanks Dave.
This is great, so much I have learned from reading this newsgroup

But interestingly, you can convert a range to proper in one fell swoop.

Selection.Value = Application.Proper(Selection.Value)

(well, I thought it was interesting!)

Thanks, I think I did not word my question clearly. I was wondering if there
was a way that does not use For Each .... Next loop, something like myArray
= UCase(myArray) [I know this does not work] that would change the case of
the selection to Upper case with one line of code or something that is
faster than a For Each .... Next loop

One way:

Dim rCell As Range
For Each rCell In Range("A1:A20")
rCell.Value = UCase(rCell.Text)
Next rCell

Hi,

With the following, I was able to change a multi-row
selection to proper case

dim myArray as variance

range("a1:a20").select
set myArray = selection
myArray = application.proper(myArray)

This does not work with UCase. Apart from using For Next
loop, is there a way similar to above code that works. I'm
looking for an alternative that works faster than For Next
loop

Thanks

 

[Dave Peterson]

It didn't work in xl2002 for me.

This has come up a couple times before and I think the speculation is that since
VBA has uCase() and lCase(), it won't let application.upper even get close to
running.

But there is no pCase() (propercase??), so it's quite happy to let
application.proper through.

 

[JE McGimpsey]

Yup, that's why I wrote "if you could"

 

[Dave Peterson]

I read it as a personal challenge--not a indictment(?) of application.upper
<vbg>.

(that maybe I couldn't spell application.upper correctly????)