PDA

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