Results 1 to 3 of 3

Thread: VBA Macro - use variable in code [INDEX(UPPER()]
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Nov 2013
    Location
    Denver, Colorado - XFD1048576 - MORE Stuff here near the end. And what if you add even more stuff a
    Posts
    617
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Happy Excelling ,

    goesr

    To enter code in your post use the format below. Click the link for more detail.
    [CODE] ... Your Code Here ... [/CODE]

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by aLUPIN View Post
    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 & "),)")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •