Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    get address of non-contiguous cells (Excel 2000/2002)

    I use range names a lot and have one set for a group of four cells. These four cells are not contiguous and I can't seem to find the right code to query each cell's address. Each cell contains a comment I want to fetch.

    Example:<pre>Dim rng as Range, cmt as String

    Set rng = Activesheet.Range("_someCells")
    cmt = rng.cells(1,1).comment.text</pre>

    This works fine for the FIRST cell in the range, but not for the other 3.

    rng.address gives me "$F$14,$G$17,$F$22,$G$28" so how can I step thru each address?

    rng.cells(2,1) gives me $G$15 but I want $G$17 (the next cell in the range), etc.

    How do you step thru a non-contiguous range of cells? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> This should be trivial to do so I'm sure I'm missing something obvious.

    Thnx, Deb

  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: get address of non-contiguous cells (Excel 2000/2002)

    You must go thru ALL the AREAS going thru all the CELLS in each AREA.
    rng.areas(1).cells(1).comment.text
    rng.areas(2).cells(1).comment.text

    Your range has 4 AREAS each with ONLY one cell.

    To be generic you can use rng.areas.count to get the number of areas
    and rng.areas(x).cells.count to get the number of cells in area x

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: get address of non-contiguous cells (Excel 2000/2002)

    You can also use a For Each loop:

    Dim rng As Range
    Dim rngCell As Range
    Set rng = ActiveSheet.Range("_someCells")
    For Each rngCell in rng
    MsgBox rngCell.Address
    Next rngCell

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get address of non-contiguous cells (Excel 2000/2002)

    Great!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> I've never used (or heard of) Areas. That's perfect for this particular use. The For Loop won't work in this case because at any given time I need to fetch any comment in any order so sequencing thru them isn't a benefit here.

    FYI. The usage here is that I store the text for a pop-up help button assigned to specific cells. When the user clicks the help button next to the cell, the code fetches the comment field in a hidden sheet which stores the help text. That way I can give admin access to someone to write their own cell context help system instead of hard-coding the text it in the code itself. The user didn't want regular cell comments but a regular pop-up help dialog to appear.

    Thnx, Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get address of non-contiguous cells (Excel 2000/2002)

    <hr>I use range names a lot <hr>
    Are you using my Name Manager yet? Find it at the Excel MVP Page below. (just uploaded a bugfix too)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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