Using PROPER for Columns, rows or ENTIRE spreadsheet

The function PROPER() works well for changing single cells to the proper
case. What I need is to convert the entire spreadsheet, or specific columns
or rows, to the proper case.
That is, if column C has some or all names in a variety of cases, I'd like
to change that to column C with all proper. Sort of a PROPER(Column C).
Same for row 12 to be PROPER(Row 12).
I'm getting data that is mainly uppercase which includes the state ID, such
as TX for Texas, etc. I don't want this to be made proper. But the names,
cities, addresses, and some descriptions in the data, I'd like to convert to
proper format.
Does there exist a utility or a way to structure the PROPER function so I
don't have to do each cell?
TIA,
Tom

This might prove useful. I have it in my personal.xls

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"Tom" <[email protected]> wrote in message
news:[email protected]...
> The function PROPER() works well for changing single cells to the proper
> case. What I need is to convert the entire spreadsheet, or specific
columns
> or rows, to the proper case.
> That is, if column C has some or all names in a variety of cases, I'd like
> to change that to column C with all proper. Sort of a PROPER(Column C).
> Same for row 12 to be PROPER(Row 12).
> I'm getting data that is mainly uppercase which includes the state ID,
such
> as TX for Texas, etc. I don't want this to be made proper. But the
names,
> cities, addresses, and some descriptions in the data, I'd like to convert
to
> proper format.
> Does there exist a utility or a way to structure the PROPER function so I
> don't have to do each cell?
> TIA,
> Tom

Thanks Don,
Since I've not put macros into Excel before, where does this go and how do I
invoke it?
Thanks again!!!
Tom

"Don Guillett" wrote:

> This might prove useful. I have it in my personal.xls
>
> Sub ChangeCase()
> Application.ScreenUpdating = False
> Dim r As Range
> nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
> lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
> Case Desired"))
> Select Case nCase
> Case "L"
> For Each r In Selection.Cells
> If r.HasFormula Then
> r.Formula = LCase(r.Formula)
> 'R.Formula = R.Value
> Else
> r.Value = LCase(r.Value)
> End If
> Next
>
> Case "U"
> For Each r In Selection.Cells
> If r.HasFormula Then
> r.Formula = UCase(r.Formula)
> 'R.Formula = R.Value
> Else
> r.Value = UCase(r.Value)
> End If
> Next
> Case "P"
>
> For Each r In Selection.Cells
> If r.HasFormula Then
> r.Formula = Application.Proper(r.Formula)
> 'R.Formula = R.Value
> Else
> r.Value = StrConv(r.Value, vbProperCase)
> End If
> Next
> End Select
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Tom" <[email protected]> wrote in message
> news:[email protected]...
> > The function PROPER() works well for changing single cells to the proper
> > case. What I need is to convert the entire spreadsheet, or specific
> columns
> > or rows, to the proper case.
> > That is, if column C has some or all names in a variety of cases, I'd like
> > to change that to column C with all proper. Sort of a PROPER(Column C).
> > Same for row 12 to be PROPER(Row 12).
> > I'm getting data that is mainly uppercase which includes the state ID,
> such
> > as TX for Texas, etc. I don't want this to be made proper. But the
> names,
> > cities, addresses, and some descriptions in the data, I'd like to convert
> to
> > proper format.
> > Does there exist a utility or a way to structure the PROPER function so I
> > don't have to do each cell?
> > TIA,
> > Tom
>
>
>

Thanks, Don!!!
This works EXACTLY what I needed.....
Thanks
Tom

"Tom" wrote:

> Thanks Don,
> Since I've not put macros into Excel before, where does this go and how do I
> invoke it?
> Thanks again!!!
> Tom
>
> "Don Guillett" wrote:
>
> > This might prove useful. I have it in my personal.xls
> >
> > Sub ChangeCase()
> > Application.ScreenUpdating = False
> > Dim r As Range
> > nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
> > lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
> > Case Desired"))
> > Select Case nCase
> > Case "L"
> > For Each r In Selection.Cells
> > If r.HasFormula Then
> > r.Formula = LCase(r.Formula)
> > 'R.Formula = R.Value
> > Else
> > r.Value = LCase(r.Value)
> > End If
> > Next
> >
> > Case "U"
> > For Each r In Selection.Cells
> > If r.HasFormula Then
> > r.Formula = UCase(r.Formula)
> > 'R.Formula = R.Value
> > Else
> > r.Value = UCase(r.Value)
> > End If
> > Next
> > Case "P"
> >
> > For Each r In Selection.Cells
> > If r.HasFormula Then
> > r.Formula = Application.Proper(r.Formula)
> > 'R.Formula = R.Value
> > Else
> > r.Value = StrConv(r.Value, vbProperCase)
> > End If
> > Next
> > End Select
> > Application.ScreenUpdating = True
> > End Sub
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Tom" <[email protected]> wrote in message
> > news:[email protected]...
> > > The function PROPER() works well for changing single cells to the proper
> > > case. What I need is to convert the entire spreadsheet, or specific
> > columns
> > > or rows, to the proper case.
> > > That is, if column C has some or all names in a variety of cases, I'd like
> > > to change that to column C with all proper. Sort of a PROPER(Column C).
> > > Same for row 12 to be PROPER(Row 12).
> > > I'm getting data that is mainly uppercase which includes the state ID,
> > such
> > > as TX for Texas, etc. I don't want this to be made proper. But the
> > names,
> > > cities, addresses, and some descriptions in the data, I'd like to convert
> > to
> > > proper format.
> > > Does there exist a utility or a way to structure the PROPER function so I
> > > don't have to do each cell?
> > > TIA,
> > > Tom
> >
> >
> >