Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    set range app error (Excel 2002)

    Hi all
    I need to know if there is something wrong with the way that I have set this range, I'm trying to call this sub from another.

    Thanks Darryl.

    Public Sub Unit2Shade()
    Dim irange As Range
    Set irange = Worksheets(1).Range(Cells(5, 2), Cells(6, 5)) 'FULL
    With irange
    .Interior.ColorIndex = 24
    End With
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: set range app error (Excel 2002)

    It is fine if the active sheet is worksheets(1) otherwise you are trying to set a range based on 2 cell references (implicit) which assume the active sheet anmd this will give an error.

    A more generic form would be:
    with worksheets(1)
    Set irange = .Range(.Cells(5, 2), .Cells(6, 5))
    end with

    since all the range/cell references are explicit.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: set range app error (Excel 2002)

    Thanks,
    I actually figured it out, because they are implicit, does that mean you set the object at the lowest level?

    Darryl.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: set range app error (Excel 2002)

    The Cells method by itself refers to cells on the currently active sheet. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: set range app error (Excel 2002)

    I do not know what you mean by "set the object at the lowest level".

    Implicit means you did not state what sheet the "cells" belonged to and so the "activesheet" is assumed.

    Your line:
    Set irange = Worksheets(1).Range(Cells(5, 2), Cells(6, 5))

    is equivalent to
    Set irange = Worksheets(1).Range(activesheet.Cells(5, 2), activesheet.Cells(6, 5))
    [The "range" object is explicitly stated to be on the worksheets(1) sheet, but the 2 "cells" have no explicit reference. Implicitly it is assumed to be the activesheet.]

    This gives an error when the explicit range (worksheets(1)) is not the same as the implicit start/end cells (activesheet).

    Note that the workbook name is not explicitly stated so the active workbook is assumed for all. Your line is also equivalent to:
    Set irange = Activeworkbook.Worksheets(1).Range(Activeworkbook. activesheet.Cells(5, 2), Activeworkbook.activesheet.Cells(6, 5))

    The activeworkook is also an implicit reference, referring to the active application (excel), etc

    Steve

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: set range app error (Excel 2002)

    Thank you thank you...

    I get it now.

    Thanks,
    Darryl.

Posting Permissions

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