Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select cell within range (Excel 2007)

    In visual basic, if I have a range of cells named "Something", then how do I select the 5th cell or any cell within that range?
    Example: For each item in range("Something")... selects each cell one by one, whereas I want to go directly to the 5th cell or someother cell.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select cell within range (Excel 2007)

    range("Something").cells(5).select

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to do this outside of VB?

    I would just like a cell to display a specific value from a range.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='AngelaJ' post='764413' date='09-Mar-2009 18:54']Is there a way to do this outside of VB?

    I would just like a cell to display a specific value from a range.[/quote]
    Try a formula such as

    =INDEX(Something,5)

    where Something is a range reference or defined name.

    If it is a rectangular range, you can use

    =INDEX(Something,4,3)

    to refer to the cell in the 4th row, 3rd column of the range Something.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, you are beautiful! I couldn't figure out what to search on and wasted 45 minutes trying to figure it out -thank you!

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    follow-on question: must named ranges always be referred to globally throughout the workbook? Or can there be the same range named on each tab, referring to the data in that tab?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='AngelaJ' post='764492' date='09-Mar-2009 23:22']follow-on question: must named ranges always be referred to globally throughout the workbook? Or can there be the same range named on each tab, referring to the data in that tab?[/quote]
    You can make a name local to a worksheet by prefixing the name with the name of the [s]workbook[/s] worksheet and an exclamation mark.
    E.g. you could have names Sheet1!MyName and Sheet2!MyName
    Or use Jan Karel Pieterse's excellent free Name Manager add-in - this lets you change defined names from global to local and back with ease.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='764495' date='09-Mar-2009 18:34']You can make a name local to a worksheet by prefixing the name with the name of the [highlight=yellow]workbook [/highlight]and an exclamation mark.[/quote]

    Hans
    Did you not mean worksheet?
    Regards
    Don

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='764516' date='10-Mar-2009 01:30']Hans
    Did you not mean worksheet?[/quote]
    Yes, you are correct (as is clear from the examples I posted). I will correct my reply.

    Thanks!

Posting Permissions

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