Macro for Proper function
 

Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the formula
to correct the cell with the information in it.
Thanks!

Macro for Proper function
 

Something like this should work for you (just change the ProperRange address
and SheetName assignments in the Const statements to match your actual
conditions)...

Sub MakeProperCase()
Dim R As Range, C As Range
Const ProperRange As String = "D:D"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set R = Intersect(.Columns(ProperRange), .UsedRange)
For Each C In R
C.Value = StrConv(C.Value, vbProperCase)
Next
End With
End Sub

--
Rick (MVP - Excel)


"stickcc" wrote in message
...
Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the
formula
to correct the cell with the information in it.
Thanks!

Macro for Proper function
 

Thanks. I copied your macro into the source for the sheet I was working on
but I'm not really sure what to change.
Here's an example of a sheet I am working on:
Sheet name is Financials. (This is sheet 3).
I want to check column A.
What do I put where?

Once I get this figured out (with help!) I'd like to be able to save the
macro to use on other spreadsheets.

"Rick Rothstein" wrote:

Something like this should work for you (just change the ProperRange address
and SheetName assignments in the Const statements to match your actual
conditions)...

Sub MakeProperCase()
Dim R As Range, C As Range
Const ProperRange As String = "D:D"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set R = Intersect(.Columns(ProperRange), .UsedRange)
For Each C In R
C.Value = StrConv(C.Value, vbProperCase)
Next
End With
End Sub

--
Rick (MVP - Excel)


"stickcc" wrote in message
...
Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the
formula
to correct the cell with the information in it.
Thanks!

Macro for Proper function
 

You have asked for a macro which Rick has provided...If you are new to macros
launch VBE using Alt+F11. Insert a module. Paste the macro. Modify the
sheetname variable, Range (Rick has referred to column D) to suit your
requirements...Save and get back to workbook. Try running the macro from
Tools|Macro ....and see.


You can also use the worksheet function PROPER(). Try this with A1 = "this
is a test"

=PROPER(A1)

If this post helps click Yes
---------------
Jacob Skaria


"stickcc" wrote:

Thanks. I copied your macro into the source for the sheet I was working on
but I'm not really sure what to change.
Here's an example of a sheet I am working on:
Sheet name is Financials. (This is sheet 3).
I want to check column A.
What do I put where?

Once I get this figured out (with help!) I'd like to be able to save the
macro to use on other spreadsheets.

"Rick Rothstein" wrote:

Something like this should work for you (just change the ProperRange address
and SheetName assignments in the Const statements to match your actual
conditions)...

Sub MakeProperCase()
Dim R As Range, C As Range
Const ProperRange As String = "D:D"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set R = Intersect(.Columns(ProperRange), .UsedRange)
For Each C In R
C.Value = StrConv(C.Value, vbProperCase)
Next
End With
End Sub

--
Rick (MVP - Excel)


"stickcc" wrote in message
...
Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the
formula
to correct the cell with the information in it.
Thanks!

Macro for Proper function
 

Warning

If there are formulas in the range they will be values after you run the macro
Maybe no problem in your case but it is better to use this :

Select the range first before you run ot

Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Jacob Skaria" wrote in message ...
You have asked for a macro which Rick has provided...If you are new to macros
launch VBE using Alt+F11. Insert a module. Paste the macro. Modify the
sheetname variable, Range (Rick has referred to column D) to suit your
requirements...Save and get back to workbook. Try running the macro from
Tools|Macro ....and see.


You can also use the worksheet function PROPER(). Try this with A1 = "this
is a test"

=PROPER(A1)

If this post helps click Yes
---------------
Jacob Skaria


"stickcc" wrote:

Thanks. I copied your macro into the source for the sheet I was working on
but I'm not really sure what to change.
Here's an example of a sheet I am working on:
Sheet name is Financials. (This is sheet 3).
I want to check column A.
What do I put where?

Once I get this figured out (with help!) I'd like to be able to save the
macro to use on other spreadsheets.

"Rick Rothstein" wrote:

Something like this should work for you (just change the ProperRange address
and SheetName assignments in the Const statements to match your actual
conditions)...

Sub MakeProperCase()
Dim R As Range, C As Range
Const ProperRange As String = "D:D"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set R = Intersect(.Columns(ProperRange), .UsedRange)
For Each C In R
C.Value = StrConv(C.Value, vbProperCase)
Next
End With
End Sub

--
Rick (MVP - Excel)


"stickcc" wrote in message
...
Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the
formula
to correct the cell with the information in it.
Thanks!

Macro for Proper function
 

Thanks to all three of you for your help. I actually can use both macros now.

"Ron de Bruin" wrote:

Warning

If there are formulas in the range they will be values after you run the macro
Maybe no problem in your case but it is better to use this :

Select the range first before you run ot

Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Jacob Skaria" wrote in message ...
You have asked for a macro which Rick has provided...If you are new to macros
launch VBE using Alt+F11. Insert a module. Paste the macro. Modify the
sheetname variable, Range (Rick has referred to column D) to suit your
requirements...Save and get back to workbook. Try running the macro from
Tools|Macro ....and see.


You can also use the worksheet function PROPER(). Try this with A1 = "this
is a test"

=PROPER(A1)

If this post helps click Yes
---------------
Jacob Skaria


"stickcc" wrote:

Thanks. I copied your macro into the source for the sheet I was working on
but I'm not really sure what to change.
Here's an example of a sheet I am working on:
Sheet name is Financials. (This is sheet 3).
I want to check column A.
What do I put where?

Once I get this figured out (with help!) I'd like to be able to save the
macro to use on other spreadsheets.

"Rick Rothstein" wrote:

Something like this should work for you (just change the ProperRange address
and SheetName assignments in the Const statements to match your actual
conditions)...

Sub MakeProperCase()
Dim R As Range, C As Range
Const ProperRange As String = "D:D"
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set R = Intersect(.Columns(ProperRange), .UsedRange)
For Each C In R
C.Value = StrConv(C.Value, vbProperCase)
Next
End With
End Sub

--
Rick (MVP - Excel)


"stickcc" wrote in message
...
Can someone help me write a macro that I could run on spreadsheets that I
need to be sure are in Title Case? I can't figure out how to make the
formula
to correct the cell with the information in it.
Thanks!