How can I convert entire columns of text to Uppercase in Excel?
 

I've used the UPPER function successfully on individual cells, but when I try
to set up a macro with wildcards or cell references, I get an error or a
circular reference...any ideas? Thanks Excel 2000

dplantlady, have a look here, http://www.xcelfiles.com/VBA_Quick16.html
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"dplantlady" wrote in message
...
I've used the UPPER function successfully on individual cells, but when I
try
to set up a macro with wildcards or cell references, I get an error or a
circular reference...any ideas? Thanks Excel 2000

A macro is a good way to achieve this but could you not simply copy the
=UPPER() formula down in an adjacent column then copy - pastespecial values
back to the first column.

If you want a macro, put a sample of data in here and I'll give you some
code.

Nick


"dplantlady" wrote in message
...
I've used the UPPER function successfully on individual cells, but when I
try
to set up a macro with wildcards or cell references, I get an error or a
circular reference...any ideas? Thanks Excel 2000

Hi

will the following code do what you want (always test code on a copy of the
workbook first!)

sub changetoupper()
for each c in Range("A1:A100")
c.value = UCASE(c.value)
next
end sub

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"dplantlady" wrote in message
...
I've used the UPPER function successfully on individual cells, but when I
try
to set up a macro with wildcards or cell references, I get an error or a
circular reference...any ideas? Thanks Excel 2000

Once again Julie......

FYI and hopefully a tip. Are you aware with that code, if there are any
formulas in the range they will be wiped out leaving values only?

I realize OP mentioned text only but on the odd chance that some of the text
seen may be the results of formulas the following code would not affect
formulas. It will however, change case of any text returned by a formula as
well as in text-only cells.

Just select a range and run code.

Sub Upper_Case()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
cell.Formula = UCase(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 29 Apr 2005 00:38:46 +0800, "JulieD"
wrote:

Hi

will the following code do what you want (always test code on a copy of the
workbook first!)

sub changetoupper()
for each c in Range("A1:A100")
c.value = UCASE(c.value)
next
end sub

Hi Gord

thanks, i actually had a vague idea that this would happen and went looking
for the post where you told me this last time and couldn't find it so i
thought i was dreaming ... however, i've now put this post in a safe place
and won't do it again :)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Once again Julie......

FYI and hopefully a tip. Are you aware with that code, if there are any
formulas in the range they will be wiped out leaving values only?

I realize OP mentioned text only but on the odd chance that some of the
text
seen may be the results of formulas the following code would not affect
formulas. It will however, change case of any text returned by a formula
as
well as in text-only cells.

Just select a range and run code.

Sub Upper_Case()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
cell.Formula = UCase(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 29 Apr 2005 00:38:46 +0800, "JulieD"

wrote:

Hi

will the following code do what you want (always test code on a copy of
the
workbook first!)

sub changetoupper()
for each c in Range("A1:A100")
c.value = UCASE(c.value)
next
end sub

This thread is perhaps good examples of incomplete macros.
I think it is very important that someone starting out with macros
be offered good examples rather than something that just
works correctly.

1) Not protecting formulas was the big mistake, it destroys
the structure and usability of the worksheet. Though usually
recoverable if know what you did and can replace the formulas.
2) A definite range in a macro means you have to change
the range each time you run a macro, or that your data will
eventually exceed your range. Generally this is
not a good idea. Not even as an example, because it is
not something that can be used anytime, anywhere (not generic).
3) Turning off screen updating makes it so the screen doesn't have
to keep on refreshing will speed up the process, but
turning off calculation should have also been included.
BUT also provide for errors, because YOU MUST turn
calculation back on if things fail or you can mess yourself up.
4) Selection itself is NOT good enough, If I were to select
an entire column the revised macro would take 3 minutes (per column)
on my slow machine (600mHz). Reducing the cells to be
changed based on whether they are constants would
speed things up to hundredths of a second for most sheets
which usually don't have much data.
5) A worksheet solution was also suggested and it is not good
in this case because it is a one time thing that is wanted, and
the clean up to remove the extra column is a LOT of work.

For a macro that changes a selection to upper case see
Proper, and other Text changes -- Use of SpecialCells
change to upper case
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
---
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

"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Once again Julie......

FYI and hopefully a tip. Are you aware with that code, if there are any
formulas in the range they will be wiped out leaving values only?

I realize OP mentioned text only but on the odd chance that some of the text
seen may be the results of formulas the following code would not affect
formulas. It will however, change case of any text returned by a formula as
well as in text-only cells.

Just select a range and run code.

Sub Upper_Case()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
cell.Formula = UCase(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 29 Apr 2005 00:38:46 +0800, "JulieD"
wrote:

Hi

will the following code do what you want (always test code on a copy of the
workbook first!)

sub changetoupper()
for each c in Range("A1:A100")
c.value = UCASE(c.value)
next
end sub

Thanks David.

90% of the time I post your UPPER code but was slothful this time around.

Will not make that mistake again.


Gord

On Sat, 30 Apr 2005 18:26:16 -0400, "David McRitchie"
wrote:

This thread is perhaps good examples of incomplete macros.
I think it is very important that someone starting out with macros
be offered good examples rather than something that just
works correctly.

1) Not protecting formulas was the big mistake, it destroys
the structure and usability of the worksheet. Though usually
recoverable if know what you did and can replace the formulas.
2) A definite range in a macro means you have to change
the range each time you run a macro, or that your data will
eventually exceed your range. Generally this is
not a good idea. Not even as an example, because it is
not something that can be used anytime, anywhere (not generic).
3) Turning off screen updating makes it so the screen doesn't have
to keep on refreshing will speed up the process, but
turning off calculation should have also been included.
BUT also provide for errors, because YOU MUST turn
calculation back on if things fail or you can mess yourself up.
4) Selection itself is NOT good enough, If I were to select
an entire column the revised macro would take 3 minutes (per column)
on my slow machine (600mHz). Reducing the cells to be
changed based on whether they are constants would
speed things up to hundredths of a second for most sheets
which usually don't have much data.
5) A worksheet solution was also suggested and it is not good
in this case because it is a one time thing that is wanted, and
the clean up to remove the extra column is a LOT of work.

For a macro that changes a selection to upper case see
Proper, and other Text changes -- Use of SpecialCells
change to upper case
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
---
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

"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Once again Julie......

FYI and hopefully a tip. Are you aware with that code, if there are any
formulas in the range they will be wiped out leaving values only?

I realize OP mentioned text only but on the odd chance that some of the text
seen may be the results of formulas the following code would not affect
formulas. It will however, change case of any text returned by a formula as
well as in text-only cells.

Just select a range and run code.

Sub Upper_Case()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
cell.Formula = UCase(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 29 Apr 2005 00:38:46 +0800, "JulieD"
wrote:

Hi

will the following code do what you want (always test code on a copy of the
workbook first!)

sub changetoupper()
for each c in Range("A1:A100")
c.value = UCASE(c.value)
next
end sub

Good examples though of how much difference in time
may be involved.

I'm just looking at something now after looking into timings
now that I have increased RAM. One macro would run over
an hour (or hours) before so it actually becomes feasible now.
The downside is that I might not recognize bad practices as
easily.

Anyway the entire pages (proper.htm#upper was referenced)
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm
have a lot of information on speeding up macros, and
making them run better.

---

"Gord Dibben" <gorddibbATshawDOTca wrote ...
Thanks David.
90% of the time I post your UPPER code but was slothful this time around.