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

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

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

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

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

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