Often you would write macros as general as possible and make your selection
*before* you run the macro, which might be to select column B before
running the
macro to make changes to existing text strings within that selection to
Proper Case and
with the understanding that the macro will change the cell formatting for
the
entire worksheet. You need to word your request a lot more carefully so
that
someone knows what you want, and to include a summary of that in your
subject.
Don't expect that by including two macros others are going to know what you
want.
How to ask a question, Daniel Petri.
http://support.microsoft.com/kb/555375
Taking what you had you might insert the following just after the last
"End With"
would be as follows,
Dim rng As Range, cell As Range
On Error Resume Next 'In case no cells in selection
Set rng = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
For Each cell In rng
cell.Formula = StrConv(cell.Formula, vbProperCase)
Next cell
On Error GoTo 0 'Resume normal error handling
Range("A2").Select
End Sub
and good programming practice would have you place the
DIM statement near the beginning of the entire macro. You can
do formatting all at once, but you must change a cell to Proper Case
one cell at a time so you need the loop.
Having reduced the range to text cell constants it isn't really necessary
to
work with the formulas, but it does add an extra layer of protection
against your converting formulas to values.
Take a look at my page
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
So that you would have a macro that make changes later to any selection
that you make before you invoke the macro.
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:B498061E-5B8F-44ED-B750-(E-Mail Removed)...
> I'd replace
>
> Cells.Select
> With Selection.Font
>
> with
>
> With Cells.Font
>
> I try to avoid select unless absolutely necessary.
>
> HTH,
> Barb Reinhardt
>
>
> "RoadKing" wrote:
>
>> Rick:
>> Thanks for the info however, I am not a programmer and would appreciate a
>> little help. The following is a macro I created. I would like to
>> include
>> the "proper" code into the this.
>>
>> Thanks in advance
>>
>> John
>>
>> Sub PageFormat()
>> '
>> ' PageFormat Macro
>> ' Macro recorded 8/19/2007 by John Donadio
>> '
>> ' Keyboard Shortcut: Ctrl+z
>> '
>> Cells.Select
>> With Selection.Font
>> .Name = "Times New Roman"
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> End With
>> Columns("B:B").Select
>> With Selection
>> .HorizontalAlignment = xlGeneral
>> .VerticalAlignment = xlBottom
>> .Orientation = 0
>> .AddIndent = False
>> .IndentLevel = 0
>> .ShrinkToFit = False
>> .ReadingOrder = xlContext
>> .MergeCells = False
>> End With
>> With Selection
>> .HorizontalAlignment = xlCenter
>> .VerticalAlignment = xlBottom
>> .Orientation = 0
>> .AddIndent = False
>> .IndentLevel = 0
>> .ShrinkToFit = False
>> .ReadingOrder = xlContext
>> .MergeCells = False
>> End With
>> Range("A2").Select
>> End Sub
>>
>>
>>
>>
>> From: "Rick Rothstein (MVP - VB)" <(E-Mail Removed)>
>> Subject: Re: Help- Changing Case
>> Date: Sunday, August 19, 2007 12:53 PM
>>
>> > Select the cells you want to make Proper and run this small macro:
>> >
>> > Sub properize()
>> > For Each r In Selection
>> > r.Value = Application.WorksheetFunction.Proper(r.Value)
>> > Next
>> > End Sub
>>
>> Or, using "pure" VBA code...
>>
>> For Each R In Selection
>> R.Value = StrConv(R.Value, vbProperCase)
>> Next
>>
>> Rick
>>
>>
>>
Similar Threads
