I re-tested using a range of A1:M55 with a mix of formula cells and text cells.

No problems.........formulas retained and all text to UPPER case

Sub Uppercase()
For Each x In Range("A1:M55")
x.Formula = UCase(x.Formula)
Next
End Sub


Gord

On Tue, 28 Nov 2006 08:20:02 -0800, Ken wrote:

Gord ... (Happy morning) ... Excel2003

1 ... I am clueless when it comes to Code ... I record Macros only & then I
attempt to get creative with cut/paste ... (My highest respect to the Code
Writers.)

2 ... I revised Range from A1:A5 to my Range (G14:CB37)

3 ... I replaced ... x.Value = UCase(x.value) ... with ... x.Formula =
UCase(x.Formula)

4 ... I ran Macro & got Debug error which appeared to occur when the Macro
hit the 1st cell containing a formula.

Above said ... my Range is G14:CB37 with Formulas in Cols U, AJ, AY, BN & CC.

The Macro I found for this in the Knowledge appeared to work fine except it
was clearing my Formula's in the columns indicated.

So ... with this new found knowledge of my shortcomings in the understanding
of Code ... Can you tell me what further edits I need to make? ... Many
Thanks ... Kha





"Gord Dibben" wrote:

Make the alteration like such............

For Each x In Range("A1:A5")
x.Formula = UCase(x.Formula)


Gord Dibben MS Excel MVP

On Mon, 27 Nov 2006 07:47:02 -0800, Ken wrote:

Excel2003 ... following Code found in the Knowledge Base ... I altered Range
& Code works well ... Except: I have some Cols within my Range that contain
Forumula's which are being erased ...

Above said ... How do I edit this Code to properly work on multiple Ranges
... as I am thinking I need to omit the cells containing Formulas.

Macro to Change All Text in a Range to Uppercase Letters

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:A5")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.value)
Next
End Sub

Thanks ... Kha



Gord Dibben MS Excel MVP