View Full Version : Solved: UCase Function
YellowLabPro
08-26-2006, 02:50 PM
I need a little help getting the values in this range changed to Uppercase.
I am trying to use the Ucase worksheet function.
Sub Calculate()
Dim ws As Worksheet
Dim LRow As Long
Dim UpperCase
Set ws = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
'Variable
LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
ws.Range.WorksheetFunction.UCase("C3:AG" & LRow).UCase
ws.Calculate
End Sub
Many thanks,
YLP
mdmackillop
08-26-2006, 03:26 PM
Hi VBAPup
VBA has its own uCase. I don't believe you can change all of a range in one command; you'll need to loop though the cells. If you're dealing with a very large range, maybe SpecialCells can identify the cells to be processed.
Set rg = Range("C3:AG" & LRow)
For Each cel In rg
cel.Value = UCase(cel)
Next
mdmackillop
08-26-2006, 03:32 PM
I've just confirmed that you'll lose your formulae with this process so SpecialCells is essential
Set rg = Range("C3:AG" & LRow).SpecialCells(xlCellTypeConstants, 2)
For Each cel In rg
cel.Value = UCase(cel)
Next
YellowLabPro
08-26-2006, 04:21 PM
Thanks Malcolm....
I have another one coming.... this one is really confusing. I had it working and then it started misbehaving... I will post as a new issue.
YLP
Bob Phillips
08-27-2006, 04:44 AM
I've just confirmed that you'll lose your formulae with this process so SpecialCells is essential
Set rg = Range("C3:AG" & LRow).SpecialCells(xlCellTypeConstants, 2)
For Each cel In rg
cel.Value = UCase(cel)
Next
or you could just test it
For Each cel In rg
If Not cel.HasFormula Then
cel.Value = UCase(cel)
End If
Next
Zack Barresse
08-29-2006, 09:04 AM
@YLP: One comment about one of your lines of code ...
LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
This is a good method, but not fully qualified. This is because you have a Rows reference that is not referenced to anything. Now this will work everytime if you have any workbook open, but if you use this in an addin, you may not always have an open workbook, then this line will fail you. To fully qualify, add the worksheet reference to all range references ..
LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
This code may also fail if the workbook is closed..
Sub Calculate()
Dim ws As Worksheet, LRow As Long, c as Range
Set ws = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator") 'Variable
LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
For Each c in ("C3:AG" & LRow)
If Not c.HasFormula Then
c.Value = UCase(c.Value)
End If
Next c
ws.Calculate '? do you need this?
End Sub
YellowLabPro
08-29-2006, 01:52 PM
Thanks Zack,
This is a very helpful point.
LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
This is a good method, but not fully qualified. This is because you have a Rows reference that is not referenced to anything. Now this will work everytime if you have any workbook open, but if you use this in an addin, you may not always have an open workbook, then this line will fail you. To fully qualify, add the worksheet reference to all range references ..
LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
This code may also fail if the workbook is closed..
Your last question regarding if the Calculate is needed?
Yes, I leave my sheets in a manual calculation state and then add this line to update individual sheets. It saves processing time when I have some of my other large workboods open....
YLP
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.