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

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 Removed)



"Roger Converse" <(E-Mail Removed)> wrote in message
news:8A0C8DDE-C2C7-4365-BD54-(E-Mail Removed)...
> 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

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" wrote:

> 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

Works perfectly.

Thanks!
Roger

"Nigel" wrote:

> 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 Removed)
>
>
>
> "Roger Converse" <(E-Mail Removed)> wrote in message
> news:8A0C8DDE-C2C7-4365-BD54-(E-Mail Removed)...
> > 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
>