Reading Active Cell Value in a For Next Loop
Reading Active Cell Value in a For Next Loop
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Reading Active Cell Value in a For Next Loop

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    The following code works as needed with one problem - RNAME is only loading Values in the first ActiveCell range. The value of RNAME is not changing to reflect the "next cells" value in the For Next loop.

    ' Range("A1").Select
    y = 1
    x = 1
    ' Position to last Column of WorkSheet.
    Selection.End(xlToRight).Select
    z = ActiveCell.Column

    'Select Range.
    Range(Cells(x, y), Cells(x, z)).Select

    For Each Cell In Selection
    RName = ActiveCell.Offset(1, 0).Value
    .
    . 'Other work here (Need ActiveCell value)
    .

    Next Cell
    (RName is not Changing after next cell???





    EMSS

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add activecell.offset(1,0).select after
    RName = ActiveCell.Offset(1, 0).Value
    You're not scrolling through the activecell values, only the cells in the selection.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just adding that line might not work, but it should make you see that the for each ... next statement doesn't actually activate cells. The active cell is the original cell that is active and it's offsetting 1 row everytime and giving you the same name. You need to add a line that will offset the activecell by one every loop. That's what I meant by my first response.

    Hope this helps,

    Dave

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try:

    RName = Cell.Value

    instead of

    RName = ActiveCell.Offset(1, 0).Value

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    90
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Dave - thanks.

    Your second explanation was what I needed. I thought next cell activated the next cell...

    Here is the code that now is working...

    For Each Cell In Selection
    RName = ActiveCell.Offset(1, 0).Value

    ActiveCell.Offset(0, 1).Select_ ActiveWorkbook.Names.AddName:=RName,_ RefersToR1C1:="=SalesDetail!C2"

    Next Cell

    I am building a large Reference Name library based on an import of file. What a time saver...

    Thanks again.

User Tag List

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
  •  

 

 
DMCA.com