+ Reply to Thread
Results 1 to 5 of 5

How to use variable inside Range?

  1. #1
    Registered User
    Join Date
    09-26-2010
    Location
    Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    How to use variable inside Range?

    Hi All,

    I am trying to create an array with certain column values of a row (as they contain some dates which I have to compare), for e.g. in my excel sheet I want "N,O,P,Q,R,S,T,V,X,Y,Z" columns value of row '3' in an array. I have several thousand of rows in the excel, so I would have to do same thing on each row. I have tried something like this, using a while loop I am iterating through each row and to track the row number I maintain a counter let suppose 'i' and capturing above columns as:
    Range("Ni,Oi,Pi,Qi,Ri,Si,Ti,Vi,Xi,Yi,Zi").Select, but this statement is giving error as value of variable 'i' is not getting substituted.

    Is it possible to substitute the value of 'i' inside Range() function? If not then how can I get these columns value in an array?


    Thanks in advance
    Amit

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: How to use variable inside Range?

    hi amithreat
    Welcome to the forum
    maybe something like ...
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    09-26-2010
    Location
    Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to use variable inside Range?

    Hey thanks a lot pike, its working!!

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: How to use variable inside Range?

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: How to use variable inside Range?

    Hello amithreat,

    Here is another method. This will select the cells "N10:T10,V10,X10:Z10" on the Active Sheet. The Union method is used to create a range of non contiguous cells. The Resize method allows you to change the number of Rows and Columns for a Range or a single cell.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ 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