Use of UCase

  • Thread starter Thread starter sylink
  • Start date Start date
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
 
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
 
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)
 
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
 
Back
Top