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

  • Thread starter Thread starter Barry
  • Start date Start date
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?
 
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 ?
 
Afraid not! That's not much better than my explanation about having to
change each cell manually. But thanks for the reply.
 
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
 
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
 
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.
 
Try your code on a range with formulas and see what happens...
Click to expand...

Point taken (formulas are converted to values) - thanks; easily fixed:
rngCell.Formula = UCase(rngCell.Formula)

OTOH:
 
Back
Top