Change a range to Uppercase

[Roger Converse]

Hello,

How would I update a range of cells to all uppercase?

My range is:

r = Range("A17:K" & i)

How could I create a loop or something that would change all cells in that
range to uppercase?

Thank you,
Roger

 

[Nigel]

Sub test()
Dim c As Range, r As Range, i As Long

i = 100 ' set for my test only!

Set r = Range("A17:K" & i)
For Each c In r
c.Value = UCase(c.Value)
Next

End Sub

 

[GB]

One way (though not the only one) would be to establish a nested loop of two
variables one for the row and one for the column.

for i= (row start) to (row end)
for j = (column start) to (column end)
cell(i, j).text = Ucase(cell(i, j).text)
next j
next i

(my usage of cell(i, j) may need to be cells(i, j) or range (cells(i, j),
cells(i, j)) or even further activesheet.cells or activesheet.range) but it
at least gives the idea. Sorry that I haven't verified the exact usage of
cell(i, j).text but the function you are looking for is the UCASE() function.

 

[Roger Converse]

Works perfectly.

Thanks!
Roger

Sub test()
Dim c As Range, r As Range, i As Long

i = 100 ' set for my test only!

Set r = Range("A17:K" & i)
For Each c In r
c.Value = UCase(c.Value)
Next

End Sub


--

Regards,
Nigel
(e-mail address removed)