Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Using Offset with Ranges

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am still new to learning VBA. I want to be able to copy a cell, move it over a certain number of rows/colums (via the offset function) and then paste the cell into 3 different consecutive columns. How do I write the code- do I integrate the range function into the offset function?

    Thanks!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I reckon you're well on the way there.
    For example, to copy the contents of Cell A10 to Cells B11 through F11...

    Range("A10").Select
    Selection.Copy

    Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 5)).Select
    ActiveSheet.Paste

    Rgds
    AJ

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Play with something like this:

    Code:
    Sub cpy()
    ActiveCell.Copy ActiveCell.Offset(1, 4).Resize(, 3)
    End Sub
    Like offset, resize has two paramters, rows & columns. You can change activecell to a range (e.g., [a1].Copy [a1].Offset(1, 4).Resize(, 3))

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-02 08:24 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cool!

Some videos you may like

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
  •