uppercase (solved)

[NCay]

How do I convert an existing sheet's data to all uppercase?
This message was edited by NCay on 2002-09-17 21:14

 

[white6174]

JUST USE THE UPPER FUNCTION

UPPER(C2)

STEVE W

 

[NCay]

what...for each individual cell!? I need to do the whole sheet.

 

[Jay Petrulis]

Hi,

Try the following code:

<pre>Sub test()
Dim Cell As Range

For Each Cell In ActiveSheet.UsedRange
If Not Cell.HasFormula Then Cell = UCase(Cell)
Next Cell

End Sub</pre>

 

[anno]

or use the format painter (standard toolbar). type something in upper case into a cell and with this cell selected, double click the format painter. click on the top left cell you want 'uppered', hold down the shift key and click on the bottom right cell to be uppered and press enter.
hth
This message was edited by anno on 2002-09-17 20:34

 

[NCay]

Anno, thought you may have become my saviour there but...that pastes whatever I type across every cell selected, I need to keep the original data and just uppercase it all, so close but yet so far...

 

[anno]

so it does. bugger. sorry about that. i was going to suggest an array using =UPPER() but you lose all your cell formatting. does jay's vba suggestion not work for you?

...that pastes whatever I type across every cell selected,

This message was edited by anno on 2002-09-17 20:51

 

[anno]

ok, slightly more convoluted way to do this but it should work. before you do this though note that if you have any formulas they will be gone.

select a range of cells the same size as those you want to change to upper case. in the formula bar type =UPPER(your cell range) and press ctrl+shift+enter simultaneously. now copy the array, select the first cell of your old range and go to edit>paste special>values. this should make everything upper case and preserve formats.

 

[NCay]

So close and yet so PERFECT, thanks a lot, it works a dream.

 

[phantom1975]

Here's an easier way to do it with a macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A5").Value = UCase(Range("A5").Value)
End Sub

Instead of A5, simply put the array that you need.