CAPITALISE GLOBALLY

[Prasad Gopinath]

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

 

[Mike H]

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

 

[Mike H]

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]

Mike

Forgive my ignorance. What is a "Module"?

Prasad

 

[Mike H]

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

 

[Gord Dibben]

Alt + F11 to open VBEditor.

CTRL + r to open Porject Explorer.

Right-click your workbook/project and Insert>Module.

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