The code below runs well when used with the worksheet function
'PROPER' When substituted with the function 'UCase', it aborts. How do
I tweak the code to run with UCase.


ActiveCell.CurrentRegion.Columns("A:A").Select
RowCount = Application.Count(Columns(1))

For Each rng In Range("H3:H" & RowCount + 3)
rng.Value = Application.WorksheetFunction.UCase(rng.Value)

Next

UCase is not a worksheet function - it is VB. Use ..
Application.WorksheetFunction.UPPER() or the *VB*
rng.Value = UCase(rng.Value)

Hi,

And I suggest this instead to prevent formula being converted to values.

For Each rng In Range("H3:H" & RowCount + 3)
rng.Formula = UCase(rng.Formula)

Mike

"sylink" wrote:

The code below runs well when used with the worksheet function
'PROPER' When substituted with the function 'UCase', it aborts. How do
I tweak the code to run with UCase.


ActiveCell.CurrentRegion.Columns("A:A").Select
RowCount = Application.Count(Columns(1))

For Each rng In Range("H3:H" & RowCount + 3)
rng.Value = Application.WorksheetFunction.UCase(rng.Value)

Next

For Each rng In Range("H3:H" & RowCount + 3)
rng.Formula = UCase(rng.Formula)

This will throw an error if rng is part of an array formula that
returns an array of values to multiple cells. Better to test if
HasFormula is True, and if so, skip rng completely. E.g.,

If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 17 Nov 2008 03:49:01 -0800, Mike H
wrote:

Hi,

And I suggest this instead to prevent formula being converted to values.

For Each rng In Range("H3:H" & RowCount + 3)
rng.Formula = UCase(rng.Formula)

Mike

"sylink" wrote:

The code below runs well when used with the worksheet function
'PROPER' When substituted with the function 'UCase', it aborts. How do
I tweak the code to run with UCase.


ActiveCell.CurrentRegion.Columns("A:A").Select
RowCount = Application.Count(Columns(1))

For Each rng In Range("H3:H" & RowCount + 3)
rng.Value = Application.WorksheetFunction.UCase(rng.Value)

Next

On Nov 17, 12:49*pm, Mike H wrote:
Hi,

And I suggest this instead to prevent formula being converted to values.

*For Each rng In Range("H3:H" & RowCount + 3)
* * * *rng.Formula = UCase(rng.Formula)

Mike

"sylink" wrote:
The code below runs well when used with the worksheet function
'PROPER' When substituted with the function 'UCase', it aborts. How do
I tweak the code to run with UCase.

ActiveCell.CurrentRegion.Columns("A:A").Select
*RowCount = Application.Count(Columns(1))

* * * * For Each rng In Range("H3:H" & RowCount + 3)
* * * *rng.Value = Application.WorksheetFunction.UCase(rng.Value)

* * Next

Worked excellently. Thanks