Change Text Case
 

I want to be able to run a Macro that chnages all the cell contents from
UPPERCASE to Titlecase. I know hwo to do it in Word but can this be done in
Excel?

Tia


Jonathan

Change Text Case
 

Hi Johnathan,

Try:

'===============
Sub MakeProperCase()
Dim rCell As Range
On Error Resume Next
For Each rCell In selection.Cells
If Not rCell.HasFormula Then
rCell.value = Application.Proper(rCell.value)
Else
ActiveCell.Formula = Application.Proper(ActiveCell.Formula)
End If
Next myCell
End Sub
'<<===============

If this is a frequent requirement, you might wish to add a toolbar button
and assign the macro to the new button.

---
Regards,
Norman



"Jonathan" wrote in message
...
I want to be able to run a Macro that chnages all the cell contents from
UPPERCASE to Titlecase. I know hwo to do it in Word but can this be done
in
Excel?

Tia


Jonathan

Change Text Case
 

Hi Norman,

Thanks for the code it works but it is taking for ever to run becuase I have
a large amount of data, rather than using a loop is there a way I can acheive
the same results but using a selection of columns i.e. A:G only?

"Norman Jones" wrote:

Hi Johnathan,

Try:

'===============
Sub MakeProperCase()
Dim rCell As Range
On Error Resume Next
For Each rCell In selection.Cells
If Not rCell.HasFormula Then
rCell.value = Application.Proper(rCell.value)
Else
ActiveCell.Formula = Application.Proper(ActiveCell.Formula)
End If
Next myCell
End Sub
'<<===============

If this is a frequent requirement, you might wish to add a toolbar button
and assign the macro to the new button.

---
Regards,
Norman



"Jonathan" wrote in message
...
I want to be able to run a Macro that chnages all the cell contents from
UPPERCASE to Titlecase. I know hwo to do it in Word but can this be done
in
Excel?

Tia


Jonathan

Change Text Case
 

Hi Jonathan,

Try:
'====================
Public Sub MakeProperCase()
Sub MakeProperCase()
Dim ws As Worksheet
Dim rng As Range
Dim rCell As Range

Set ws = ActiveSheet

On Error Resume Next
Set rng = Columns("A:G").SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0

If Not rng Is Nothing Then

Application.ScreenUpdating = False

For Each rCell In rng.Cells
rCell.Value = Application.Proper(rCell.Value)
Next rCell

Application.ScreenUpdating = True

End If

End Sub
'<<====================

---
Regards,
Norman



"Jonathan" wrote in message
...
Hi Norman,

Thanks for the code it works but it is taking for ever to run becuase I
have
a large amount of data, rather than using a loop is there a way I can
acheive
the same results but using a selection of columns i.e. A:G only?

"Norman Jones" wrote:

Hi Johnathan,

Try:

'===============
Sub MakeProperCase()
Dim rCell As Range
On Error Resume Next
For Each rCell In selection.Cells
If Not rCell.HasFormula Then
rCell.value = Application.Proper(rCell.value)
Else
ActiveCell.Formula = Application.Proper(ActiveCell.Formula)
End If
Next myCell
End Sub
'<<===============

If this is a frequent requirement, you might wish to add a toolbar button
and assign the macro to the new button.

---
Regards,
Norman



"Jonathan" wrote in message
...
I want to be able to run a Macro that chnages all the cell contents from
UPPERCASE to Titlecase. I know hwo to do it in Word but can this be
done
in
Excel?

Tia


Jonathan