Driving around cells whilst in a macro
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Driving around cells whilst in a macro

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Evening All,
    I assume this is an easy request.
    If I wish to move to the cell on the right or move multiple cells left, what is the correct code?
    Many thanks in advance.
    S

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 15:45, Sam40mUK wrote:
    Evening All,
    I assume this is an easy request.
    If I wish to move to the cell on the right or move multiple cells left, what is the correct code?
    Many thanks in advance.
    S
    Hi,

    Many ways to do this. What exactly do you want?

    ActiveCell.Offset(row,col) is a common method.

    Post more detals for a more complete answer.

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Jay,
    Thanks for the swift response.
    Basically I am attempting to End Down to the bottom of a list, then move left two cells and paste a Summary.
    The list is a product of a Pivot Table and therefore is of variable length .. hence the End Down and variable nature of left two cells, paste.
    I hope this clarifies the situation a little.
    Regards
    S.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 15:59, Sam40mUK wrote:
    Hello Jay,
    Thanks for the swift response.
    Basically I am attempting to End Down to the bottom of a list, then move left two cells and paste a Summary.
    The list is a product of a Pivot Table and therefore is of variable length .. hence the End Down and variable nature of left two cells, paste.
    I hope this clarifies the situation a little.
    Regards
    S.
    Hi,

    Range("C2").End(xlDown).Offset(0, -2) = summary value

    or

    ActiveCell.End(xlDown).Offset(0, -2) = summary value

    This requires that this is run with the Active Cell in the correct column.

    Not necessarily the best way, though.

    Assuming you have only one Pivot Table on the sheet in columns A:E, then you could do something like the following in your code:

    -------------
    lastrow = Cells(rows.count,"A").end(xlup).row
    Cells(lastrow,"Your Column") = Summary
    -------------

    This finds the last row and places the
    summary in the column of your choice. You could also make the column a determined variable as well.

    lastcol = Cells(2,256).End(xltoLeft).Column

    and reference 2 columns to the left of that

    Cells(lastrow + 5, lastcol - 2) = summary val

    for example.

    HTH,
    Jay


    [ This Message was edited by: Jay Petrulis on 2002-04-09 16:15 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Cheers Jay,
    I will have to give your reply a looking at tomorrow.
    Meantime thank you for your effort and help.
    S

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