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" wrote:

> 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?
>
>
>
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

If this is a one time thing, this may be the easiest suggestion that you get.

Maybe these links to Debra Dalgleish's site that shows how to fill a range would
make it easier.

http://contextures.com/xlDataEntry01.html#Quick
http://contextures.com/xlDataEntry01.html#Mouse

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

--

Dave Peterson

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

On Tue, 22 May 2007 20:30:35 -0500, "Barry" <(E-Mail Removed)> wrote:

>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
>

On Tue, 22 May 2007 20:30:35 -0500, Barry wrote in
microsoft.public.excel:

>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

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.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Try your code on a range with formulas and see what happens...


In article <(E-Mail Removed)>,
Michael Bednarek <ROT13-(E-Mail Removed)> wrote:

> 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.
>

On Tue, 22 May 2007 22:34:59 -0600, JE McGimpsey wrote in
microsoft.public.excel:

>Try your code on a range with formulas and see what happens...

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

OTOH:
On Tue, 22 May 2007 19:37:06 -0500, Barry wrote in
microsoft.public.excel:

>I have a workbook of Contacts. In column A there are Names. I wish to
>convert those to UPPER case.

>In article <(E-Mail Removed)>,
> Michael Bednarek wrote:
>
>> 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.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"