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

look in vba help index for SPECIALCELLS. Then Constants

--
Don Guillett
SalesAid Software

"Ken" wrote in message
...
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

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 ... (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

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

Gord ... (Good afternoon)

FYI ... Looks simple enough when you know what you are doing ... Macro is
working fine now ... Not sure what I did before (or didn't do?), but then ...
This is why I come to the Wizards of this board ... Thank you ... Kha

"Gord Dibben" wrote:

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

Gord ... (Hi again)

FYI ... Everything still workingfine ... My 1st shot @ editing the Macro as
you suggested should have also worked fine ... I am certain the initial
"Debug" issue was do to a "Protected" Sheet (Macro ok until it hit 1st Locked
Cell) ... I guess I should have paid more attention to the pop-up message ...
Such is life ... Thank you again for the guidance ... Kha

"Gord Dibben" wrote:

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

Ken

Thanks for the feedback and letting us know the reason why you had a failure.

If you wish to run it on a protected sheet you can code it so's when you run it,
the sheet is mometarily unprotected, the text gets changed to UPPER case then
re-protected.

Your password may not be "justme"....adjust to suit.

Sub Uppercase()
ActiveSheet.Unprotect Password:="justme"
For Each x In Range("A1:M55")
x.Formula = UCase(x.Formula)
Next
ActiveSheet.Protect Password:="justme"
End Sub


Gord

On Tue, 28 Nov 2006 10:54:40 -0800, Ken wrote:

Gord ... (Hi again)

FYI ... Everything still workingfine ... My 1st shot @ editing the Macro as
you suggested should have also worked fine ... I am certain the initial
"Debug" issue was do to a "Protected" Sheet (Macro ok until it hit 1st Locked
Cell) ... I guess I should have paid more attention to the pop-up message ...
Such is life ... Thank you again for the guidance ... Kha

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP