Solved: UCase Function

[YellowLabPro]

I need a little help getting the values in this range changed to Uppercase.

I am trying to use the Ucase worksheet function.

[VBA]
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
[/VBA]

Many thanks,

YLP

[mdmackillop]

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.

[VBA]
Set rg = Range("C3:AG" & LRow)
For Each cel In rg
cel.Value = UCase(cel)
Next

[/VBA]

[mdmackillop]

I've just confirmed that you'll lose your formulae with this process so SpecialCells is essential
[VBA]
Set rg = Range("C3:AG" & LRow).SpecialCells(xlCellTypeConstants, 2)
For Each cel In rg
cel.Value = UCase(cel)
Next

[/VBA]

[YellowLabPro]

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]

I've just confirmed that you'll lose your formulae with this process so SpecialCells is essential
[vba]
Set rg = Range("C3:AG" & LRow).SpecialCells(xlCellTypeConstants, 2)
For Each cel In rg
cel.Value = UCase(cel)
Next

[/vba]

or you could just test it

[vba]

For Each cel In rg
If Not cel.HasFormula Then
cel.Value = UCase(cel)
End If
Next
[/vba]

[Zack Barresse]

@YLP: One comment about one of your lines of code ...

[vba]LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row[/vba]

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 ..

[vba]LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row[/vba]

This code may also fail if the workbook is closed..

[VBA]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[/VBA]

[YellowLabPro]

Thanks Zack,
This is a very helpful point.

[vba]LRow = ws.Cells(Rows.Count, 3).End(xlUp).Row[/vba]

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 ..

[vba]LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row[/vba]

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