changing case
MIKE R
row or column? Having trouble.
Ron de Bruin
http://www.cpearson.com/excel/case.htm
Or
http://www.mvps.org/dmcritchie/excel/proper.htm
Here are some Macro's for the selection
Sub Uppercase_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 = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Lowercase_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 = LCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
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
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"MIKE R" <mrab...@starband.net> wrote in message
news:2e7401c2efd8$7f287f10$3001...@phx.gbl...
David McRitchie
You already have your answer from Ron's code or in my code.
On my webpage proper.htm each line of code is explained
in the webpage comments. You can also customize a macro
for propercase so that you see "Ron de Bruin" and "David McRitchie"
instead of the less desired "Ron De Bruin" and "David Mcritchie"
directly from the Proper function.
In the consider...
When you indicate you have trouble you did not indicate what the trouble
is and what the code is. If for instance your code were similar to
Chip's code, the macro would take forever if you selected entire
columns. Simply choosing entire columns would take about 8 minutes
per column selected on my computer. Simpler is not always faster,
use of SpecialCells or use of UsedRange greatly reduces the time,
as does turning off screen updating and calculation. .
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Ron de Bruin" <ronde...@kabelfoon.nl> wrote ...
> See these webpages [clipped]
> Here are some Macro's for the selection [clipped]
>
> "MIKE R" <mrab...@starband.net> wrote ...
Dave Peterson
ok for me:
Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Intersect(Range("g:G"), ActiveSheet.UsedRange)
myRng.Value = Application.Proper(myRng.Value)
End Sub
If you run this against any cell with a formula, it'll convert it to a constant.
(The same technique doesn't work with application.upper and application.lower.)
MIKE R wrote:
>
> How do you change the text case to PROPER for an entire
> row or column? Having trouble.
--
Dave Peterson
ec3...@msn.com
David McRitchie
Don't know how you came up with a no loop solution, but here
is a more generic version of your macro to work with a selection
including an entire row or column as was asked for. There is a
test included that will prevent abnormal termination if the selection
is outside the used range.
Sub MakeProper_Quick()
'Dave Peterson, 2003-03-21, misc, no loop required...
'-- doesn't work with application.upper and application.lower
Dim myRng As Range
Set myRng = Intersect(Selection, ActiveSheet.UsedRange)
If Not myRng Is Nothing Then _
myRng.Formula = Application.Proper(myRng.Formula)
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dave Peterson" <ec3...@msn.com> wrote in message news:3E7BA8E4...@msn.com...
Dave Peterson
application.proper worked this way and .upper didn't (except for that's the way
xl works).
The closest I could guess is that since UCase and LCase are built into VBA, the
developers wouldn't allow application.upper/.lower to be used this way.
I think that this is a little more safe since it won't touch the formula cells.
(But the loop is back!)
Option Explicit
Sub MakeProper_Quick()
'Dave Peterson, 2003-03-21, misc, no loop required...
'-- doesn't work with application.upper and application.lower
Application.ScreenUpdating = False
Dim myRng As Range
Dim myArea As Range
On Error Resume Next
Set myRng = Intersect(Selection, ActiveSheet.UsedRange) _
.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No constants"
Else
For Each myArea In myRng.Areas
myArea.Value = Application.Proper(myArea.Value)
Next myArea
End If
Application.ScreenUpdating = True
End Sub
--
Dave Peterson
ec3...@msn.com
David McRitchie
I had made a change where .formula appears on both
sides of the formula so no formulas are changed to values.
Your change is not looping cells but is looping areas, which
in Excel 2000 is not necessary. It would be necessary in
Excel 95 if you have multiple areas selected. Your error
message would more correctly be "no text constants".
There appears to be a problem that I can't pinpoint with *some*
large selections that overlap the usedrange and the unused area.
The only one that I can consistently see fail is to select all cells
and then run the macro. My modification and your later macro with
areas both fail with
Run-time error '13':
Type mismatch
Another problem I can't pinpoint nor even know which of the
two macros it occurred with but a lot of text values changed
to #N/A! causing additional problems with some cells that
had dependent formulas. I think I had seen the changes to
proper without a loop before and such mysterious problems
turned me against using it. In anycase I like to make additional
changes for my own lasname so it appears properly so I need
the loop as in the first subroutine on my proper.htm page.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dave Peterson" <ec3...@msn.com> wrote in message news:3E7BD68B...@msn.com...
> It came up in a similar thread a few months ago. I couldn't explain why
> application.proper worked this way and .upper didn't (except for that's the way
> xl works).
>
> The closest I could guess is that since UCase and LCase are built into VBA, the
> developers wouldn't allow application.upper/.lower to be used this way.
>
> I think that this is a little more safe since it won't touch the formula cells.
> (But the loop is back!) [... clipped...]
Dave Peterson
If I didn't loop through the areas, I'd get the values of the first area stuck
into the other areas (assuming more than one area). Some of the cells in the
other areas got filled with the values--sometimes the it was a combination of
the values and #n/a's.
Just in case you want to duplicate what I did:
I filled A1:H29 with this formula:
="asdf "&ADDRESS(ROW(),COLUMN(),4)&" qwer"
I got a bunch of values that looked like:
asdf A1 qwer
(with the middle section varying)
Then I converted a bunch of formulas to values:
I did a ctrl-A to select all, then f5, special, constants
From the immediate window in the VBE:
?selection.address
$B$5:$F$17,$H$6:$H$12,$D$21:$D$25,$G$20:$G$26,$B$23:$B$27
I selected A1:J32 (included cells outside the usedrange) and ran this macro:
Option Explicit
Sub MakeProper_Quick2()
Application.ScreenUpdating = False
Dim myRng As Range
On Error Resume Next
Set myRng = Intersect(Selection, ActiveSheet.UsedRange) _
.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
myRng.Value = Application.Proper(myRng.Value)
Application.ScreenUpdating = True
End Sub
$B$5:$F$17 (looked ok)
$H$6:$H$12
showed this:
Asdf B5 Qwer
Asdf B6 Qwer
Asdf B7 Qwer
Asdf B8 Qwer
Asdf B9 Qwer
#N/A
#N/A
$D$21:$D$25
showed this:
Asdf B5 Qwer
Asdf B6 Qwer
Asdf B7 Qwer
Asdf B8 Qwer
Asdf B9 Qwer
$G$20:$G$26
showed this:
Asdf B5 Qwer
Asdf B6 Qwer
Asdf B7 Qwer
Asdf B8 Qwer
Asdf B9 Qwer
#N/A
#N/A
$B$23:$B$27
showed this:
Asdf B5 Qwer
Asdf B6 Qwer
Asdf B7 Qwer
Asdf B8 Qwer
Asdf B9 Qwer
So the first column of the first area was (kind of) propagated to the other
areas. I don't know why excel changed some of the values to #n/a, though.
(I use xl2k at work and xl2002 at home. I've seen enough posts that show
working with multiple areas in a range changed behavior in different versions of
excel (I don't recall if it was xl97 to xl2k or xl2k to xl2002, though).
But looping through the areas in the range is a small price to pay not to have
to remember these kinds of details <vbg>.
I ran the same macro with only the .usedrange selected and got the same
results. I didn't have any trouble with selecting a range that was outside the
.usedrange.
And I never did a test against a single selected cell, but that would have been
bad. But this version seems to work ok:
Option Explicit
Sub MakeProper_Quick()
'Dave Peterson, 2003-03-21, misc, no loop required...
'-- doesn't work with application.upper and application.lower
Application.ScreenUpdating = False
Dim myRng As Range
Dim myArea As Range
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No Text Constants"
Else
For Each myArea In myRng.Areas
myArea.Value = Application.Proper(myArea.Value)
Next myArea
End If
Application.ScreenUpdating = True
End Sub
(I used a technique that I learned from a guy whose initial's are DM!)
--
Dave Peterson
ec3...@msn.com
David McRitchie
I had used similar data generated by my MarkCells and adding
a prefix and had not noticed that the converted data had been
from another area but had noticed the mysterious #N/A! errors
that destroyed the data making the solution useless.
So I can see that the processing separate areas you used is
needed. (my Excel is 2000)
You can convert formulas as well as text constants if you use
.formula on both sides of the assignment. Changed the error
message to match the situation. Don't see need for
On Error Resume Next since there is a test for a valid range.
I put your test data creation into a macro to generate test
data and implement the MakeProper_Quick macro.
Option Explicit
Sub MakeProper_Quick_test()
Range("A1").Formula = "=""asdf ""&ADDRESS(ROW(),COLUMN(),4)&"" qwer"""
Dim i As Long
i = InputBox("type 1 to convert all to values", "values", 1)
If i = 1 Then
Cells.Copy
Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:H1"), Type:=xlFillDefault
Range("A1:H1").Select
Selection.AutoFill Destination:=Range("A1:H29"), Type:=xlFillDefault
Range("A1:H29").Select
Range("B5:F17,H6:H12,D21:D25,G20:G26,B23:B27").Select
Range("B23").Activate
Application.Run "MakeProper_Quick"
End Sub
Sub MakeProper_Quick()
'Dave Peterson, 2003-03-21, misc, no loop required...
'-- doesn't work with application.upper and application.lower
Application.ScreenUpdating = False
Dim myRng As Range
Dim myArea As Range
On Error Resume Next
Set myRng = Intersect(Selection, ActiveSheet.UsedRange)
'On Error GoTo 0
If myRng Is Nothing Then
MsgBox "Nothing in intersect range"
Else
For Each myArea In myRng.Areas
myArea.Formula = Application.Proper(myArea.Formula)
Next myArea
End If
Application.ScreenUpdating = True
End Sub
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dave Peterson" <ec3...@msn.com> wrote in message news:3E7C62B9...@msn.com...
Dave Peterson
I don't like touching formulas. (I kept changing to .value and you kept
changing back to .formula <bg>).
If the formula contains literals, then I can see your point. But if the formula
contains a reference to another cell, then it doesn't make that string proper.
(But I guess it comes down to what you want it to do.)
I used to have a version that would convert the formula to =proper(oldformula)
if I found a formula. The bad news with that is that I converted numeric
entries to text. That's when I decided (for me) that I'd only touch the
constants.
And since I was limiting my changes to constants, I had to protect against not
having any in the range--hence the "on error" stuff.
--
Dave Peterson
ec3...@msn.com
Dana DeLouis
technique of using "Evaluate" on a range of cells.
Excel XP has made it easier for those who want to use this technique.
However, one has to be careful when using this technique because the first
cell in the first Area is used as a reference.
If one does not evaluate a range correctly with the correct formula, the
value in the first cell is copied to all the other cells. However, I have
never figured out the logic that Excel is using when using the technique you
describe. You will get different results based on what Excel considers the
"First" area.
For example, with [A1:C3] and [G10:H12], then Excel most likely will
consider [A1:C3]
as the first "Area"
But, if it's [A20:C22] and [G10:H12], then the first area might be
[G10:H12].
I do not have the answer, but Excel XP has made it easier to "Map" a
function onto a range of cells.
What didn't work well in earlier versions, now works. One of the nicest
enhancements to Excel XP is the elimination of the Size restriction.
I remember in Excel 97, non-looping techniques required not only looping on
the "Areas," but also on the "Size" of the area. One had to break up an
area if it was too big.
In Excel XP, one can now apply a function to a range of cells greater than
the earlier array size limit of around 5,461 elements.
Another thing to mention (I really do not know here) is that Excel XP did
fix a "small" problem.
In VBA, to get a list of WorksheetFunctions, one had to type...
"Application.WorksheetFunction."
...that last "period" brought up a list of available functions.
In Excel XP, Microsoft fixed this, and now one can just type...
"WorksheetFunction." and this last period brings up the list of
available functions.
VBA no longer has to go thru the "Application" to get to the functions. I
really do not know, but I have a feeling this small little change has
something to do with it.
This works well for evaluation. However, it has has always been interesting
to see
that the technique you mention requires "Application.Function." (I have
never understood this).
Although I like this technique, the other technique of evaluating a range
can use functions such as
Upper, Lower, If, etc. For those who really don't like program loops, one
can also adjust a range of cells based on the values in other cells. I
don't think this is possible with this technique. For those who like to
program with "Mathematica," one can make a very loose analogy by saying that
the Worksheet function "Offset" is "Listable", meaning it can be mapped
(evaluated) onto a range of cells.
Not sure if you are aware, but "Proper" is not the only function that is
"Evaluable" (my term for "Listable") <vbg>
Here, one can apply the function Ceiling to a range of cells. This Rounds a
range of numbers up to the nearest integer. This adjusts formulas to keep
the code short.
Sub Demo()
'// Dana DeLouis
Dim rng As Range
Set rng = [A1:A20]
rng.Formula = "=10 * RAND()"
rng.EntireColumn.AutoFit
rng = Application.Ceiling(rng, 1)
End Sub
Just some references if interested.
XL: Maximum Array Size in Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;166342
XL97: Custom Function Returns #VALUE! If Returning Array Larger Than 5,461
Elements
http://support.microsoft.com/default.aspx?scid=kb;en-us;216531
Non-Looping is a special interest of mine. :>)
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3E7CAA5D...@msn.com...