Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem accessing range (2000 sp-3/ 98SE)

    I'm using some code that works fine if the sheet concerned (strSheet) is the active one, but which fails if not:
    Runtime error 1004: Application-defined or object-defined error.
    I can't see how the active sheet has any bearing on how I've specified the range :
    <code>
    With ThisWorkbook.Worksheets(strSheet)
    Set rng = .Range(Cells(RowLast, ColLeft), Cells(RowLast, ColLast))
    </code>
    All of the variables in the expressions above contain valid and correct values when the error occurs in the "Set rng" statement. Any help appreciated.

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    .Range refers to ThisWorkbook.Worksheets(strSheet), but Cells(...) refers to the active worksheet. Prefixing both occurrences of Cells(...) with a point will make them refer to ThisWorkbook.Worksheets(strSheet) too.

    <code>Set rng = .Range(.Cells(RowLast, ColLeft), .Cells(RowLast, ColLast))</code>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Thank you for pointing <img src=/S/pun.gif border=0 alt=pun width=22 height=18> that out so quickly Hans. I'm not used to working with ranges specificied like this. The myriad of range syntaxes is a mini learning curve all by itself. But once I see your comparison in type, it's all so clear. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    cheers
    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    This particular trap is one I fell into several times when I started programming in Excel, I try to be alert for it now.

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Hans

    I'm trying to use the amended code you provided for Alan but I'm still getting the same error message.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    What's your specific code Rob? Maybe your leg's caught in a different trap to the one that snagged mine.

    Alan

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Do you have the With statement from Alan's post AND the Set statement from Hans' Post? You have to have both for it to work.
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Yes to both questions, Legare.
    Originally, I couldn't get passed the first line - Subscript out of range until I enclosed the strSheet part in quotes. Now the second line causes a 1004 error.
    I am using:

    With ThisWorkbook.Worksheets("strSheet")
    Set rng = .Range(.Cells(RowLast, ColLeft), .Cells(RowLast, ColLast))
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Just playing around, Alan, trying to see how things work.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Is strSheet the actual name of a worksheet or a string variable? If the latter, it shouldn't be in quotes.
    Have the variables RowLast, ColLeft and ColLast been assigned valid values?

  11. #11
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    Got it now. Thanks Hans.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem accessing range (2000 sp-3/ 98SE)

    I see you've cracked it Rob. I didn't really explain what were variable names and what were string literals. I've just started playing around (for necessity, rather than fun) with some of the rich syntaxes related to ranges. I'm sure there will be some good dedicated web refs to all this (Help is not very detailed). In fact, there's so much there you could probably invent some cryptic puzzles with it! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Glad you got it working anyway.

    Alan

Posting Permissions

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