Macro Help (Uppercase multiple ranges?)

[Ken]

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

 

[Don Guillett]

look in vba help index for SPECIALCELLS. Then Constants

 

[Gord Dibben]

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

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


Gord Dibben MS Excel MVP

 

[Ken]

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]

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

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 MS Excel MVP

 

[Ken]

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

 

[Ken]

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]

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

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 MS Excel MVP

 

[Gord Dibben]

Ken

Why do you not just run it as is?

It is not the macro I posted that takes 60 mins to run.

Looks like you're running it as a part of another macro from another workbook
text.xls

What else is going on?

Can you post your entire macro for the group to peruse?

Or send me the workbook to my email with an explanation of what is to occur?

Change the AT and DOT to unmung my email.


Gord

Gord ... (Good morning)

Need further guidance ... This little Macro working fine ... However, it
takes approx .60 mins to run ... That said, I would like an option imbedded
in my Recorded Macro to run or not.

Remember, I know nothing about Code ... I record Macros only ... Then its
cut/paste.

Above said ...

1: I copied Macro you provided into File Module where my Recorded Macros
resides.
2: I recorded a new Macro to Run this application
3: I inserted the recorded "Run Application instruction" into my Macro
4: I placed an "Unprotect" instruction above the Run App
5: I placed a "Protect" instruction after the Run App

Looks like this (another copy/paste):

ActiveSheet.Unprotect
Application.Run _
"'OT-(Test).xls'!Uppercase"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Above said ... is there now a way to insert a YES/NO above these lines of
Code ... Where the NO will not EXIT the Code ... but will SKIP around it &
continue to run the remaining lines of my recorded Macro?

Code
Code
Code
YES/NO ... (YES to run next 4 lines ... NO to skip next 4 lines)
ActiveSheet.Unprotect
Application.Run _
"'OT-(Test).xls'!Uppercase"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Code
Code
Code

I am sure this is much easier than I make it out to be ... But such is life
...

Thanks for the support ... Kha

Gord Dibben MS Excel MVP

 

[Gord Dibben]

Kha

Sorry....I missed the decimal point.......thought it was period after "approx"

To answer the question about choosing to run or not run the Uppercase
macro.......

Sub whatever()
'code
'code
'code
msg = "Do You Want to run Uppercase macro?" & Chr(13) _
& "If Yes, this action will add time to the procedure."
Ans = MsgBox(msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
GoTo carryon
Case vbNo
GoTo skipit
End Select
carryon:
'run the uppercase macro
skipit:
'without the Upercase macro
'code
'code
'code

End Sub


Gord

 

[Kha]

Gord ... (Happy Saturday)

I will try this (Monday) ... I know much of this thread is consequence of my
lack of knowledge regarding Code & correct terminology ... so please know I
appreciate your patience & guidance ...

Say I have 1000 lines of Code (all mine is recorded ... then cut/paste) ...
I have Code where Code will run to a point ... A message box appears (Yes/No)
.... The Yes ... continues running the Code ... The No ... Exits the Code.

Above said ... If I want to "spike" out a section of Code in the middle to
Run/Not Run (rather than Exit) ... (ie: Run lines 1-399 ... message box
(Yes/No) ...

Yes ... Run lines 400-1000
No .... Skip lines 400-600 ... Run lines 601-1000

How do I achieve this on random Marcros where I might want to implement? ...
or ... will the Macro instruction contained in your last post achieve this
for me as written?

Note: I revised my display name from "Ken" to "Kha" to assist me with my
searches of this board ... Another shortcoming ...

Thanks ... Have a Happy & Safe weekend ... Kha

 

[Ken]

Gord ... (Hi)

I am learning every day ... changing my Ident back to "Ken" ... looks like
as long as I imbed "Kha" in my post I will be all set with my searches ...

It is a great day in the neighborhood ... Thanks ... Kha

 

[Gord Dibben]

Move the "carryon:" and "skipit:" lines to where you want the macro to go
to after the msgbox is responded to.

There are far more elegant and efficient methods to achieve your ends but I am
not as proficient with coding as some others that hang around here or in the
programming group.


Gord

Gord ... (Happy Saturday)

I will try this (Monday) ... I know much of this thread is consequence of my
lack of knowledge regarding Code & correct terminology ... so please know I
appreciate your patience & guidance ...

Say I have 1000 lines of Code (all mine is recorded ... then cut/paste) ...
I have Code where Code will run to a point ... A message box appears (Yes/No)
... The Yes ... continues running the Code ... The No ... Exits the Code.

Above said ... If I want to "spike" out a section of Code in the middle to
Run/Not Run (rather than Exit) ... (ie: Run lines 1-399 ... message box
(Yes/No) ...

Yes ... Run lines 400-1000
No .... Skip lines 400-600 ... Run lines 601-1000

How do I achieve this on random Marcros where I might want to implement? ...
or ... will the Macro instruction contained in your last post achieve this
for me as written?

Note: I revised my display name from "Ken" to "Kha" to assist me with my
searches of this board ... Another shortcoming ...

Thanks ... Have a Happy & Safe weekend ... Kha

Gord Dibben MS Excel MVP