Results 1 to 5 of 5

Thread: Referencing a range that is not on the active sheet.

  1. #1
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0

    Referencing a range that is not on the active sheet.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've got some code that says this:
    Code:
    Set TabOrderRange = Sheet6.Range("Controls", Cells(iTabOrderCount + 1, 3))
    "Controls" is a one cell named range, that serves as a "Put the data here" handle.

    This works fine if sheet6 is the active sheet, but fails otherwise with this error: Method 'Range' of object '_Worksheet' failed. Obviously I don't quite have a handle on how to qualify references. Can anyone tell me where I'm going wrong here?

  2. #2
    Try

    Code:
    Set TabOrderRange = Sheet6.Range("Controls", Sheet6.Cells(iTabOrderCount + 1, 3))

  3. #3
    Or even

    Code:
    With Sheet6
    
    Set TabOrderRange = .Range(.Range("Controls"), .Cells(iTabOrderCount + 1, 3))
    End With

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Perfect. Thanks Bob...it's like you're sitting right next to me. Why the heck should one need to reference the particular sheet the cells are on inside the RANGE arguement as well as outside of it? THere's so many little things like this to rote learn....profficiency in VBA is definately hard won.

  5. #5
    The first Sheet6.Range refers to an overall range, but the second is its own range object and by not qualifying it defaults to the activesheet, so you are trying to refer to a range on a specified sheet where part of the range is on another sheet, hence the error.

Posting Permissions

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