Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    I was mildly surprised that the code Hans used here worked with the line
    Code:
    Range(oCell, Range("Z65536")).ClearContents
    without the address property of oCell (as shown below), used.
    Code:
    Range(oCell.Address, Range("Z65536")).ClearContents
    But work it did. This led me to do a little testing through which I discovered that the following code works through line 30, but fails at line 40.
    Code:
    Sub test()
    		   Dim oCell As Range
    	 10		Set oCell = Cells(4, 1)
    	 20		Range(oCell.Address) = Timer
    	 30		Range(oCell, oCell) = Timer
    	 40		Range(oCell) = Timer
    	 End Sub
    Can someone help me understand what is going on?
    [attachment=83572:Range_Fail.gif]
    Attached Images Attached Images
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The Range property has two forms:

    1) Range(reference) where reference is a string that can contain a cell address, a range address or a defined name.

    Examples:

    Range("A1")
    Range("A1:C10")
    Range("NorthEast")

    Of course, you can use a string variable or expression instead of a literal reference.

    2) Range(cell1, cell2) where cell1 and cell2 are Range objects that refer to a single cell.

    Examples:

    Range(Range("A1"), Range("C10"))
    Range(Cells(1, 1), Cells(10, 3))

    You can use variables of type Range instead of explicit range objects.

    Your example in line #20 uses the first syntax: the single argument oCell.Address is a string. This is correct.
    Line #30 uses the second syntax: both arguments are of type Range. This is correct too.
    Line # 40 fails because you try to mix the two forms: you use a single argument but it's a range object instead of a string value.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='773467' date='02-May-2009 20:46'][/quote]Thank you Hans.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By the way, the second syntax is more forgiving than the first one: it allows you to use string arguments:

    Range("A1", "C10")

    and to mix string and range arguments:

    Range("A1", Range("C10")

    And if one or both of the arguments refers to more than one cell, you'll get the smallest rectangular range that encompasses both arguments:

    Range(Range("A1:C5"), Range("B37")) is the same as Range("A17").

  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
    Note that there is a somewhat major difference between the lines of code:
    Range(oCell, Range("Z65536")).ClearContents

    Range(oCell.Address, Range("Z65536")).ClearContents

    In the first, since the other ranges are implicitly defined as being on the active worksheet, that if oCell is an object on a different worksheet, a runtime error will occur.

    In the second line of code, using the address property you only get the cell address, unlinked to a particular sheet and this will work no matter what sheet oCell is on. If oCell is on a different sheet, however, its contents will not be cleared as you are not referencing oCell, but the address of ocell on the activesheet.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='sdckapr' post='773473' date='02-May-2009 22:02']Note that there is a somewhat major difference between the lines of code:
    Range(oCell, Range("Z65536")).ClearContents

    Range(oCell.Address, Range("Z65536")).ClearContents

    In the first, since the other ranges are implicitly defined as being on the active worksheet, that if oCell is an object on a different worksheet, a runtime error will occur.

    In the second line of code, using the address property you only get the cell address, unlinked to a particular sheet and this will work no matter what sheet oCell is on. If oCell is on a different sheet, however, its contents will not be cleared as you are not referencing oCell, but the address of ocell on the activesheet.[/quote]
    Thanks Steve; a subtly which I completely missed.
    Regards
    Don

Posting Permissions

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