Change case for all text in cell range

[R. Lehr]

I'm trying to change all the names in existing cells (in a contiguous range)
from standard case to all upper case (e.g: A1 = Joe Smith, A2 = Jane Doe,
etc. to: A1 = JOE SMITH, A2 = JANE DOE, etc.). I need the new upper case
values to be in the original cells.

Using "=UPPER(A1:BB82) forces the values in that range to change but the new
upper case values are in a different cell range.

Any ideas?

Thanks in advance.
RLL ~ 1/27/2004

 

[beeawwb]

Borrowing code from Dave Peterson on truncating Left, this will make
everything in A1:BB86 uppercase.

Sub UCaseMe()
Application.ScreenUpdating = False
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = [A1:BB82]
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
Else
For Each myCell In myRng.Cells
myCell.Value = UCase(myCell.Value)
Next myCell
End If
Application.ScreenUpdating = True
End Sub

Hope that helps.

-Bob

 

[beeawwb]

Belay that. It won't do A1:BB86, it does A1:BB82. Getting ahead of
myself.

-Bob

 

[R. Lehr]

Thanks -

Works great! Much appreciated. [And thanks to Dave Peterson on his
truncating Left part of code.]

I thought Excel had a function that could be slightly tweaked. But this
macro (sub-routine) works great.

~RLL

 

[R. Lehr]

Yes - noticed that - just too quick on the numeric keys!
Thanks again.
~RLL

 

[JE McGimpsey]

Take a look at David McRitchie's Upper() macro:

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