Use of UCase

[sylink]

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

 

[brian.baulsom]

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

 

[Mike H]

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

 

[Chip Pearson]

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)

 

[sylink]

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

Worked excellently. Thanks