Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    25

    Unanswered: .offset question - hopefully and easy one

    I am trying to get excel to go from the activecell ("A1") and select range "C2:d9" based on the following code:

    Code:
    Range(ActiveCell.Offset(1, 2), ActiveCell.Offset(8, 3)).Select
    However, when the compiler gets to that line, I get an error stating "Method 'Range of object'_Worksheet' failed". I'm sure I might be forgetting something, but can not figure out why excel will not select the cell range.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this question in the Excel forum...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2005
    Posts
    25
    nevemind, found the problem...compiler was gettting confused because the code is to work across multiple sheets and couldn't decipher which sheet to run the code on. Once I specified Activesheet.Range(Activecell.offset... ) all was fine.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Mike,

    Why not just use this?
    Code:
    ActiveSheet.Range("C2:D9").Select
    Last edited by Colin Legg; 12-06-10 at 16:26.

  5. #5
    Join Date
    Mar 2005
    Posts
    25
    I know that would've been the simple solution, but I try not to use absolute ranges in my code if at all possible. I tend to get less errors that way

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    What makes your code error prone is that it uses ActiveCell etc... which then relies upon particular ranges being selected.

    In a general sense, rather than using Offset within Range, Cells is better.
    Code:
    Sheet1.Range(Sheet1.Cells(2, 3), Sheet1.Cells(10, 5)).Select
    (Not that I'm recommending the Select method attached on the end - I'm concentrating on the Range syntax). This syntax is useful in loops, for example. But there's nothing error prone about the last example I posted.

Posting Permissions

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