Gothcha

Thanks for keeping at this Rick.

Gord

On Tue, 3 Jun 2008 13:20:26 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Uh, his code is nice, but it doesn't address (meaning it has the same flaw)
as I was attempting to point out... it upper cases **everything** in the
formula including text strings used to check text from outside the range,
even if doing so destroys the formula's intent. Using the same formula I
posted in my last message...

=IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No")

Because FIND is being used, the contents of A1 is being searched in a
case-sensitive manner; hence, "rick" needs to stay "rick" in order for the
function to do what it was designed to do. However, David's code and my
code, changed in accordance with the caveat you posted, both change the
"rick" to "RICK"... that means FIND will no longer be able to find what it
was intended to find and the formula, while still a formula, will no longer
perform as intended.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
David McRitchie's code covers all bases and runs much faster on a large
range.

Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


Gord

On Tue, 3 Jun 2008 10:27:43 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I was thinking about this a little more and realize that there is a caveat
to your caveat.<g Depending on what formulas exist in the cells (or, in
the
case of my Change event procedure, what formulas the user is attempting to
enter), it may be inappropriate to simply UCase the Formula property of
each
cell. Consider a formula like this (which relies on a case-sensitive
evaluation of some sort)...

=IF(ISNUMBER(FIND("rick",$A$1)),"Yes","No")

If we simply UCase the Formula property, the above formula will become
this...

=IF(ISNUMBER(FIND("RICK",$A$1)),"YES","NO")

and no longer work properly. The YES/NO would be correct, but the
evaluation
taking place for the contents of A1, which lies outside of the range being
UCase'd, has now been transformed to something different than intended.
So,
it is probably more correct to change this line from my code...

R.Formula = UCase$(R.Formula)

to this...

If Not R.HasFormula Then
R.Formula = UCase$(R.Formula)
End If

instead (and, of course, make the similar change to my Change event code
as
well); although I would be willing to bet, if I thought about it long
enough, that there are probably cases where this might not be the
appropriate solution either.<g

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Just a caveat with Rick's code.

If you have any formulas in the B3:E10 range they will be converted to
values.

To prevent that.

Sub UpperCaseExistingText()
Dim R As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each R In Range("B3:E10")
R.Formula = UCase$(R.Formula)
Next
Whoops:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP


On Mon, 2 Jun 2008 17:06:23 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

You can use this macro to do
that...

Sub UpperCaseExistingText()
Dim R As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each R In Range("B3:E10")
R.Value = UCase$(R.Value)
Next
Whoops:
Application.EnableEvents = True
End Sub