Change case setting from lower to upper with current complete doc

[Nae]

I have been given an excel spreadsheet which requires certain parts of the
document to be changed from lower case to upper case.
I tried using the help topic and the formula it gives you, does not inform
of where you are to type the formula i.e. in the cell where the current text
is? or any other easy ways of making these changes.

Please assist

 

[Rowan Drummond]

You can't use a formula in the same cell that contains your data so you
would have to put the formula in another (unused) cell.

So if you have "mytext" in cell A1, in cell B1 you cound enter the
formula =UPPER(A1). This will return the result "MYTEXT" in cell B1. You
could then copy B1 and pastespecial values over cell A1 before deleting
the formula from B1.

Alternately, you could use a macro to change the text in place. Select
all the cells you want changed and then run this macro:

Sub UpperCase()
Dim r As Range
For Each r In Selection
If Not r.HasFormula Then r.Value = UCase(r.Value)
Next r
End Sub

If you are new to macros see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps
Rowan

 

[Paul D. Simon]

Highlight the cell(s) you want changed and run this macro.

For Each c In Selection.Cells
c.Value = UCase$(c.Value)
Next c

 

[Paul D. Simon]

For anyone who may have read my response above, David Ritchie was kind
enough to point out to me a very dangerous and potentially disastrous
result of my solution in that my code would inadvertantly (and
unknowingly to the user) change formulas to values.

Rowan's code above works fine and avoids the pitfalls of mine.
Additionally, you can check out
http://www.mvps.org/dmcritchie/excel/proper.htm#upper