+ Reply to Thread
Results 1 to 6 of 6

setting a range as a variable

Hybrid View

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    Panama
    MS-Off Ver
    Excel 2007
    Posts
    90

    setting a range as a variable

    Good day,

    I want to select a different range based on an if statement,

    Im having issues with the Range as variable, its not taking a value,

    Dim RAC as range
    dim x as integer
    dim y as integer
    
    x = 5
    y = 4
    
        If Worksheets("Test").Range("C8") = "1" Then
            RAC = Range("A2:B" & x)
        ElseIf Worksheets("test").Range("C8") = "2" Then
            RAC = Range("A" & x + 1 & ":B" & x + y+ 1)
        End If

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: setting a range as a variable

    you need to use set
    Set RAC = ...
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: setting a range as a variable

    Object variables require Set, not Let:

    Set RAC = Range("A" & x + 1 & ":B" & x + y+ 1)
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    12-27-2012
    Location
    Panama
    MS-Off Ver
    Excel 2007
    Posts
    90

    Re: setting a range as a variable

    the compiler accepts it, however when executing i get the error "Mehtod 'Range' of object '_ Globa'l Failed

    when trying to select it,

    Range(RAC).select

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: setting a range as a variable

    RAC is a Range object not the name/address of a range so
    RAC.Select
    assuming the correct sheet is active

  6. #6
    Registered User
    Join Date
    12-27-2012
    Location
    Panama
    MS-Off Ver
    Excel 2007
    Posts
    90

    Re: setting a range as a variable

    thanks! ^^

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1