Use of UCase

S

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
 
B

brian.baulsom

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

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
 
C

Chip Pearson

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

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)
 
S

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
Click to expand...

Worked excellently. Thanks
 
Top