Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
I have a worksheet typed in upper and lower case. Is there a command I could
use to globally change it to UPPER CASE? Prasad Gopinath |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
Hi again,
Put this in a module Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange c.Value = UCase(c.Value) Next End Sub Mike "Prasad Gopinath" wrote: I have a worksheet typed in upper and lower case. Is there a command I could use to globally change it to UPPER CASE? Prasad Gopinath |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
Mike
Forgive my ignorance. What is a "Module"? Prasad "Mike H" wrote: Hi again, Put this in a module Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange c.Value = UCase(c.Value) Next End Sub Mike "Prasad Gopinath" wrote: I have a worksheet typed in upper and lower case. Is there a command I could use to globally change it to UPPER CASE? Prasad Gopinath |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
Firstly don't use the first one it will change you formula into values so
apologies for that and use the second one. To create a module tap ALT + Fll to open VB editor then right click 'this workbook' and insert module and paste your code in on the right hand side. It will then work on the active worksheet. Mike "Prasad Gopinath" wrote: Mike Forgive my ignorance. What is a "Module"? Prasad "Mike H" wrote: Hi again, Put this in a module Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange c.Value = UCase(c.Value) Next End Sub Mike "Prasad Gopinath" wrote: I have a worksheet typed in upper and lower case. Is there a command I could use to globally change it to UPPER CASE? Prasad Gopinath |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
Alt + F11 to open VBEditor.
CTRL + r to open Porject Explorer. Right-click your workbook/project and InsertModule. I will caution you against running the macro Mike provided if there might be any formulas in the usedrange. The code will change all formulas to values. Revised code to prevent that..................... Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange c.Formula = UCase(c.Formula) Next End Sub Or trap for formulas.................... Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange If Not c.HasFormula Then c.Value = UCase(c.Value) End If Next End Sub Gord Dibben MS Excel MVP On Sun, 10 Feb 2008 12:42:02 -0800, Prasad Gopinath wrote: Mike Forgive my ignorance. What is a "Module"? Prasad "Mike H" wrote: Hi again, Put this in a module Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange c.Value = UCase(c.Value) Next End Sub Mike "Prasad Gopinath" wrote: I have a worksheet typed in upper and lower case. Is there a command I could use to globally change it to UPPER CASE? Prasad Gopinath |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAPITALISE GLOBALLY
Sorry an error in that one , use this instead
Sub mersible() Set myrange = ActiveSheet.UsedRange On Error Resume Next For Each c In myrange If Not IsNumeric(c.Value) Then c.Value = UCase(c.Value) End If Next End Sub Mike "Prasad Gopinath" wrote: I have a worksheet typed in upper and lower case. Is there a command I could use to globally change it to UPPER CASE? Prasad Gopinath |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAPITALISE | Excel Discussion (Misc queries) | |||
Globally replacing * | Excel Discussion (Misc queries) | |||
Can I globally add to a worksheet? | Excel Worksheet Functions | |||
how can i capitalise text in an existing workbook | Excel Worksheet Functions | |||
Trying to Capitalise on Input to Cells | Excel Worksheet Functions |