Change Text Case to Upper in a cell range A2:A92

B

Barry

I have a workbook of Contacts. In column A there are Names. I wish to
convert those to UPPER case. I've seen a few VB codes that change the case
to UPPER if I change something in the cell, GREAT if I want to go through
the entire Column and change something. But is there another way?
 
G

gr8posts

Barry said:
I have a workbook of Contacts. In column A there are Names. I wish to
convert those to UPPER case. I've seen a few VB codes that change the case
to UPPER if I change something in the cell, GREAT if I want to go through
the entire Column and change something. But is there another way?
Click to expand...
Use function =UPPER(A1) in a temporary column and then copy paste values to
column A.
Does this help ?
 
B

Barry

Afraid not! That's not much better than my explanation about having to
change each cell manually. But thanks for the reply.
 
B

Barry

Further explanation to my requested code.

The following code does what I want only if I change something in each cell.
My hope is to use code to automatically change the text in all cells in one
effort.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TheCell As Range
Application.EnableEvents = False
For Each TheCell In Target
If TheCell.HasFormula = False Then
If Not Application.Intersect(TheCell, Range("A1:A10")) Is
Nothing Then
TheCell.Value = UCase(TheCell.Value)
End If
End If
Next TheCell
Application.EnableEvents = True
End Sub


Cordially,
Chip Pearson
 
G

Gord Dibben

Barry

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
M

Michael Bednarek

Further explanation to my requested code.

The following code does what I want only if I change something in each cell.
My hope is to use code to automatically change the text in all cells in one
effort.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TheCell As Range
Application.EnableEvents = False
For Each TheCell In Target
If TheCell.HasFormula = False Then
If Not Application.Intersect(TheCell, Range("A1:A10")) Is
Nothing Then
TheCell.Value = UCase(TheCell.Value)
End If
End If
Next TheCell
Application.EnableEvents = True
End Sub
Click to expand...

I may be missing something, but doesn't this trivial code do what you
want:

Sub SelUCase()

Dim rngCell As Range

For Each rngCell In Selection
rngCell = UCase(rngCell)
Next rngCell

End Sub

and then Alt+F8, or Menu: Tools/Macro/Macros..., or a button assigned to
the macro, to run it on the selected cells.
 
Top