Hello,

Is there a command to convert all text in a sheet to UpperCase in one shot?
Or do I have to loop?

Dim rng As Range, i As Integer, j As Integer
set rng = Sheet1.UsedRange
For i = 1 to rng.Rows.Count
For j = 1 to rng.Columns.count
rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
Next
Next

Thanks,
Rich

See

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

In article <5D2474F5-08C1-4A1F-8F8C-(E-Mail Removed)>,
Rich <(E-Mail Removed)> wrote:

> Hello,
>
> Is there a command to convert all text in a sheet to UpperCase in one shot?
> Or do I have to loop?
>
> Dim rng As Range, i As Integer, j As Integer
> set rng = Sheet1.UsedRange
> For i = 1 to rng.Rows.Count
> For j = 1 to rng.Columns.count
> rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
> Next
> Next
>
> Thanks,
> Rich

Thank you for your reply. It looks like there is no way to get around
looping through the range of text to convert it to Ucase. I ended up doing
the looping on the range thing, it was pretty slow. I was hoping I could do
something like

Sheet1.UsedRange.Text = Ucase(Sheet1.UsedRange.Text)

But that did not work out. The idea was like you can select a range of
cells and hit ctrl-B to bold everything in one shot. That it was I was
hoping to achieve with Ucase. Like create a macro that I could call with
ctrl something but without looping.



"JE McGimpsey" wrote:

> See
>
> http://www.mvps.org/dmcritchie/excel/proper.htm#upper
>
> In article <5D2474F5-08C1-4A1F-8F8C-(E-Mail Removed)>,
> Rich <(E-Mail Removed)> wrote:
>
> > Hello,
> >
> > Is there a command to convert all text in a sheet to UpperCase in one shot?
> > Or do I have to loop?
> >
> > Dim rng As Range, i As Integer, j As Integer
> > set rng = Sheet1.UsedRange
> > For i = 1 to rng.Rows.Count
> > For j = 1 to rng.Columns.count
> > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
> > Next
> > Next
> >
> > Thanks,
> > Rich
>

did you turn off screenupdating and calculation?

--


Gary


"Rich" <(E-Mail Removed)> wrote in message
news:024E8868-0086-442B-A838-(E-Mail Removed)...
> Thank you for your reply. It looks like there is no way to get around
> looping through the range of text to convert it to Ucase. I ended up doing
> the looping on the range thing, it was pretty slow. I was hoping I could do
> something like
>
> Sheet1.UsedRange.Text = Ucase(Sheet1.UsedRange.Text)
>
> But that did not work out. The idea was like you can select a range of
> cells and hit ctrl-B to bold everything in one shot. That it was I was
> hoping to achieve with Ucase. Like create a macro that I could call with
> ctrl something but without looping.
>
>
>
> "JE McGimpsey" wrote:
>
>> See
>>
>> http://www.mvps.org/dmcritchie/excel/proper.htm#upper
>>
>> In article <5D2474F5-08C1-4A1F-8F8C-(E-Mail Removed)>,
>> Rich <(E-Mail Removed)> wrote:
>>
>> > Hello,
>> >
>> > Is there a command to convert all text in a sheet to UpperCase in one shot?
>> > Or do I have to loop?
>> >
>> > Dim rng As Range, i As Integer, j As Integer
>> > set rng = Sheet1.UsedRange
>> > For i = 1 to rng.Rows.Count
>> > For j = 1 to rng.Columns.count
>> > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code
>> > whatever
>> > Next
>> > Next
>> >
>> > Thanks,
>> > Rich
>>

I found Calculations tab in Options menu item. I guess you turn it off by
checking "Manual". But how do you disable ScreenUpdating?


"Gary Keramidas" wrote:

> did you turn off screenupdating and calculation?
>
> --
>
>
> Gary
>
>
> "Rich" <(E-Mail Removed)> wrote in message
> news:024E8868-0086-442B-A838-(E-Mail Removed)...
> > Thank you for your reply. It looks like there is no way to get around
> > looping through the range of text to convert it to Ucase. I ended up doing
> > the looping on the range thing, it was pretty slow. I was hoping I could do
> > something like
> >
> > Sheet1.UsedRange.Text = Ucase(Sheet1.UsedRange.Text)
> >
> > But that did not work out. The idea was like you can select a range of
> > cells and hit ctrl-B to bold everything in one shot. That it was I was
> > hoping to achieve with Ucase. Like create a macro that I could call with
> > ctrl something but without looping.
> >
> >
> >
> > "JE McGimpsey" wrote:
> >
> >> See
> >>
> >> http://www.mvps.org/dmcritchie/excel/proper.htm#upper
> >>
> >> In article <5D2474F5-08C1-4A1F-8F8C-(E-Mail Removed)>,
> >> Rich <(E-Mail Removed)> wrote:
> >>
> >> > Hello,
> >> >
> >> > Is there a command to convert all text in a sheet to UpperCase in one shot?
> >> > Or do I have to loop?
> >> >
> >> > Dim rng As Range, i As Integer, j As Integer
> >> > set rng = Sheet1.UsedRange
> >> > For i = 1 to rng.Rows.Count
> >> > For j = 1 to rng.Columns.count
> >> > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code
> >> > whatever
> >> > Next
> >> > Next
> >> >
> >> > Thanks,
> >> > Rich
> >>
>
>
>

One way:

At the beginning of your code:

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

At the end of your code:

With Application
.Calculation =xlCalculationAutomatic
.ScreenUpdating = True
End With

In article <116A47D0-9623-4CEC-8C4F-(E-Mail Removed)>,
Rich <(E-Mail Removed)> wrote:

> I found Calculations tab in Options menu item. I guess you turn it off by
> checking "Manual". But how do you disable ScreenUpdating?
>
>
> "Gary Keramidas" wrote:
>
> > did you turn off screenupdating and calculation?
> >
> > --
> >
> >
> > Gary
> >
> >
> > "Rich" <(E-Mail Removed)> wrote in message
> > news:024E8868-0086-442B-A838-(E-Mail Removed)...
> > > Thank you for your reply. It looks like there is no way to get around
> > > looping through the range of text to convert it to Ucase. I ended up
> > > doing
> > > the looping on the range thing, it was pretty slow. I was hoping I could
> > > do
> > > something like
> > >
> > > Sheet1.UsedRange.Text = Ucase(Sheet1.UsedRange.Text)
> > >
> > > But that did not work out. The idea was like you can select a range of
> > > cells and hit ctrl-B to bold everything in one shot. That it was I was
> > > hoping to achieve with Ucase. Like create a macro that I could call with
> > > ctrl something but without looping.
> > >
> > >
> > >
> > > "JE McGimpsey" wrote:
> > >
> > >> See
> > >>
> > >> http://www.mvps.org/dmcritchie/excel/proper.htm#upper
> > >>
> > >> In article <5D2474F5-08C1-4A1F-8F8C-(E-Mail Removed)>,
> > >> Rich <(E-Mail Removed)> wrote:
> > >>
> > >> > Hello,
> > >> >
> > >> > Is there a command to convert all text in a sheet to UpperCase in one
> > >> > shot?
> > >> > Or do I have to loop?
> > >> >
> > >> > Dim rng As Range, i As Integer, j As Integer
> > >> > set rng = Sheet1.UsedRange
> > >> > For i = 1 to rng.Rows.Count
> > >> > For j = 1 to rng.Columns.count
> > >> > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code
> > >> > whatever
> > >> > Next
> > >> > Next
> > >> >
> > >> > Thanks,
> > >> > Rich
> > >>
> >
> >
> >

Hi Rich -

This is kind of a kooky way to do this, but i think it will work and should
be really fast. Try something like this:

sub convertRange2Upper(someRange as range)
dim replaceWhat as variant
dim replaceWith as variant
dim i as integer

replaceWhat = array("a","b","c",...)
replaceWith = array("A","B","C",...)

for i = 0 to 25
someRange.replace What:=replaceWhat(i), Replacement:=replaceWith(i), _

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
next
End sub

This idea came to me as I read your post, but it should work because the
search replace is very fast. I am actually not sure what the replaceFormat
does - that might be worth checking into also.

Hope that helps.

Chris (ct60)

"Rich" wrote:

> Hello,
>
> Is there a command to convert all text in a sheet to UpperCase in one shot?
> Or do I have to loop?
>
> Dim rng As Range, i As Integer, j As Integer
> set rng = Sheet1.UsedRange
> For i = 1 to rng.Rows.Count
> For j = 1 to rng.Columns.count
> rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
> Next
> Next
>
> Thanks,
> Rich

Or something like:

Option Explicit
Sub testme()
Call convertRange2Upper(ActiveSheet.Range("a:a"))
End Sub
Sub convertRange2Upper(someRange As Range)
Dim i As Long
For i = Asc("a") To Asc("z")
someRange.Replace What:=Chr(i), _
Replacement:=Chr(i - 32), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Maybe turning calculation to manual may help speed it up, too.

SearchFormat and ReplaceFormat were added in xl2002. You can actually specify
if you want the format (bold/font color/fill color) changed, too.

Try it under the Edit|Replace|Options button.


ct60 wrote:
>
> Hi Rich -
>
> This is kind of a kooky way to do this, but i think it will work and should
> be really fast. Try something like this:
>
> sub convertRange2Upper(someRange as range)
> dim replaceWhat as variant
> dim replaceWith as variant
> dim i as integer
>
> replaceWhat = array("a","b","c",...)
> replaceWith = array("A","B","C",...)
>
> for i = 0 to 25
> someRange.replace What:=replaceWhat(i), Replacement:=replaceWith(i), _
>
> LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> next
> End sub
>
> This idea came to me as I read your post, but it should work because the
> search replace is very fast. I am actually not sure what the replaceFormat
> does - that might be worth checking into also.
>
> Hope that helps.
>
> Chris (ct60)
>
> "Rich" wrote:
>
> > Hello,
> >
> > Is there a command to convert all text in a sheet to UpperCase in one shot?
> > Or do I have to loop?
> >
> > Dim rng As Range, i As Integer, j As Integer
> > set rng = Sheet1.UsedRange
> > For i = 1 to rng.Rows.Count
> > For j = 1 to rng.Columns.count
> > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
> > Next
> > Next
> >
> > Thanks,
> > Rich

--

Dave Peterson

That's a good one, Dave

Chris

"Dave Peterson" wrote:

> Or something like:
>
> Option Explicit
> Sub testme()
> Call convertRange2Upper(ActiveSheet.Range("a:a"))
> End Sub
> Sub convertRange2Upper(someRange As Range)
> Dim i As Long
> For i = Asc("a") To Asc("z")
> someRange.Replace What:=Chr(i), _
> Replacement:=Chr(i - 32), _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _
> ReplaceFormat:=False
> Next i
> End Sub
>
> Maybe turning calculation to manual may help speed it up, too.
>
> SearchFormat and ReplaceFormat were added in xl2002. You can actually specify
> if you want the format (bold/font color/fill color) changed, too.
>
> Try it under the Edit|Replace|Options button.
>
>
> ct60 wrote:
> >
> > Hi Rich -
> >
> > This is kind of a kooky way to do this, but i think it will work and should
> > be really fast. Try something like this:
> >
> > sub convertRange2Upper(someRange as range)
> > dim replaceWhat as variant
> > dim replaceWith as variant
> > dim i as integer
> >
> > replaceWhat = array("a","b","c",...)
> > replaceWith = array("A","B","C",...)
> >
> > for i = 0 to 25
> > someRange.replace What:=replaceWhat(i), Replacement:=replaceWith(i), _
> >
> > LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
> > SearchFormat:=False, _
> > ReplaceFormat:=False
> > next
> > End sub
> >
> > This idea came to me as I read your post, but it should work because the
> > search replace is very fast. I am actually not sure what the replaceFormat
> > does - that might be worth checking into also.
> >
> > Hope that helps.
> >
> > Chris (ct60)
> >
> > "Rich" wrote:
> >
> > > Hello,
> > >
> > > Is there a command to convert all text in a sheet to UpperCase in one shot?
> > > Or do I have to loop?
> > >
> > > Dim rng As Range, i As Integer, j As Integer
> > > set rng = Sheet1.UsedRange
> > > For i = 1 to rng.Rows.Count
> > > For j = 1 to rng.Columns.count
> > > rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
> > > Next
> > > Next
> > >
> > > Thanks,
> > > Rich
>
> --
>
> Dave Peterson
>