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

aLUPIN

New Member
Joined
Mar 8, 2017
Messages
6
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi there aLUPIN - you might consider the code below:

Code:
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
 
Upvote 0
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 & "),)")
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top