VBA Macro - use variable in code [INDEX(UPPER()]

[aLUPIN]

Hi Experts

I have found numerous examples of how to convert ranges/selections of cells to UpperCase, LowerCase & InitalCaps) by using a loop. Although it is much fast to have it done using the formula here:

[$G$10:$G$20] = [INDEX(UPPER($G$10:$G$20),)]

question:
How can I use the same formula for using a Variable instead of the references?

the 2 examples subjoined do not work:

Dim strSelection as Range
strSelection = Selection
[strSelection] = [INDEX(UPPER(strSelection),)]

Dim Addr As String
Addr = Selection.Address
[Addr] = [INDEX(UPPER(Addr),)]

I already searched the net, but no real answer there.
This must be simple, but it's off limits for a layman

Thx for the help

 

[XLfriend777]

Hi there aLUPIN - you might consider the code below:

Sub UCASE_aLUPIN()
Application.ScreenUpdating = False

Dim rng As Range
Dim cell As Range

Set rng = Range("A1:K20")
'rng.Select

For Each cell In rng
    cell.Value = UCase(cell)
Next cell

Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

Hi Experts

I have found numerous examples of how to convert ranges/selections of cells to UpperCase, LowerCase & InitalCaps) by using a loop. Although it is much fast to have it done using the formula here:

[$G$10:$G$20] = [INDEX(UPPER($G$10:$G$20),)]

question:
How can I use the same formula for using a Variable instead of the references?

the 2 examples subjoined do not work:

Dim strSelection as Range
strSelection = Selection
[strSelection] = [INDEX(UPPER(strSelection),)]

Dim Addr As String
Addr = Selection.Address
[Addr] = [INDEX(UPPER(Addr),)]

I already searched the net, but no real answer there.
This must be simple, but it's off limits for a layman

Thx for the help

The square brackets are a "shortcut" form of the Evaluate function for use when nothing is variable. For variable content, you must use the Evaluate function as its argument is a text string which can be concatenated together from constant and variable values. Untested, but the following should work for the two highlighted code lines...

strSelection = Evaluate("INDEX(UPPER(" & strSelection.Address & "),)")

Range("Addr") = Evaluate("INDEX(UPPER(" & Addr & "),)")