+ Reply to Thread
Results 1 to 6 of 6

Range missing its target area weirdly

  1. #1
    Konrad Viltersten
    Guest

    Range missing its target area weirdly

    I have tried two different approaches to do the same
    thing, i.e. flipping the bold format.

    Approach #1
    Set rSel = Selection
    rSel.Cells(1, 1).Font.Bold = Not rSel.Cells(1, 1).Font.Bold

    Approach #2
    Set rSel = Selection
    rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold = _
    Not rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold

    Now, i'd say they are equivalent. However, what happens
    makes me a little bit confused. When my selection starts
    from A1, both approaches work in exactly the same way.
    As soon as i start my selection from, say, B2, i get the
    following difference.
    Approach #1
    The top, leftmost element of the selection (here it's B2)
    gets changed, as expected to.
    Approach #2
    The _second_ top, leftmost element of the selection (here
    it's C3) gets changed, to my astonishment.

    It wouldn't surprise me if it was a matter of defnition of
    Range but as far as i can read the docs, there's nothing in
    there regarding this behavior. Any comments?


    --
    Vänligen
    Konrad
    ---------------------------------------------------

    Sleep - thing used by ineffective people
    as a substitute for coffee

    Ambition - a poor excuse for not having
    enough sence to be lazy
    ---------------------------------------------------


  2. #2
    Chip Pearson
    Guest

    Re: Range missing its target area weirdly

    They are different because the second approach results in an
    offset equal to rSel from rSel. So if rSel is C3,
    rSel(rSel.Cells(1,1))

    is

    rSel.Range("C3").Cells(1,1)
    or E5.

    The "C3" is not the absolute reference to cell C3, it is
    *relative* to rSel.

    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com




    "Konrad Viltersten" <[email protected]> wrote in message
    news:[email protected]...
    >I have tried two different approaches to do the same
    > thing, i.e. flipping the bold format.
    >
    > Approach #1
    > Set rSel = Selection
    > rSel.Cells(1, 1).Font.Bold = Not rSel.Cells(1, 1).Font.Bold
    >
    > Approach #2
    > Set rSel = Selection
    > rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold = _
    > Not rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold
    >
    > Now, i'd say they are equivalent. However, what happens
    > makes me a little bit confused. When my selection starts
    > from A1, both approaches work in exactly the same way.
    > As soon as i start my selection from, say, B2, i get the
    > following difference.
    > Approach #1
    > The top, leftmost element of the selection (here it's B2)
    > gets changed, as expected to.
    > Approach #2
    > The _second_ top, leftmost element of the selection (here
    > it's C3) gets changed, to my astonishment.
    >
    > It wouldn't surprise me if it was a matter of defnition of
    > Range but as far as i can read the docs, there's nothing in
    > there regarding this behavior. Any comments?
    >
    >
    > --
    > Vänligen
    > Konrad
    > ---------------------------------------------------
    >
    > Sleep - thing used by ineffective people
    > as a substitute for coffee
    >
    > Ambition - a poor excuse for not having
    > enough sence to be lazy
    > ---------------------------------------------------
    >




  3. #3
    Chip Pearson
    Guest

    Re: Range missing its target area weirdly

    As an example, run code like

    Debug.Print Range("C3").Range("C3").Address

    This will display $E$5. The second C3 indicates the offset from
    the first C3.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com





    "Chip Pearson" <[email protected]> wrote in message
    news:[email protected]...
    > They are different because the second approach results in an
    > offset equal to rSel from rSel. So if rSel is C3,
    > rSel(rSel.Cells(1,1))
    >
    > is
    >
    > rSel.Range("C3").Cells(1,1)
    > or E5.
    >
    > The "C3" is not the absolute reference to cell C3, it is
    > *relative* to rSel.
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    >
    >
    > "Konrad Viltersten" <[email protected]> wrote in message
    > news:[email protected]...
    >>I have tried two different approaches to do the same
    >> thing, i.e. flipping the bold format.
    >>
    >> Approach #1
    >> Set rSel = Selection
    >> rSel.Cells(1, 1).Font.Bold = Not rSel.Cells(1, 1).Font.Bold
    >>
    >> Approach #2
    >> Set rSel = Selection
    >> rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold = _
    >> Not rSel.Range(rSel.Cells(1, 1), rSel.Cells(1,
    >> 1)).Font.Bold
    >>
    >> Now, i'd say they are equivalent. However, what happens
    >> makes me a little bit confused. When my selection starts
    >> from A1, both approaches work in exactly the same way.
    >> As soon as i start my selection from, say, B2, i get the
    >> following difference.
    >> Approach #1
    >> The top, leftmost element of the selection (here it's B2)
    >> gets changed, as expected to.
    >> Approach #2
    >> The _second_ top, leftmost element of the selection (here
    >> it's C3) gets changed, to my astonishment.
    >>
    >> It wouldn't surprise me if it was a matter of defnition of
    >> Range but as far as i can read the docs, there's nothing in
    >> there regarding this behavior. Any comments?
    >>
    >>
    >> --
    >> Vänligen
    >> Konrad
    >> ---------------------------------------------------
    >>
    >> Sleep - thing used by ineffective people
    >> as a substitute for coffee
    >>
    >> Ambition - a poor excuse for not having
    >> enough sence to be lazy
    >> ---------------------------------------------------
    >>

    >
    >




  4. #4
    Greg Wilson
    Guest

    RE: Range missing its target area weirdly

    The Range method returns a relative reference. If unqualified it defaults to
    the active sheet and references are relative to the top-left cell - i.e.
    relative to cell A1 and Range("C3") refers to cell C3. However, if you select
    cells F5:G6 and specify Selection.Range("C3") it will return cell H7. In
    other words, "C3" specifies an offset from the top-left corner of the
    selection (F5) instead of from the top-left corner of the worksheet.

    Greg

    "Konrad Viltersten" wrote:

    > I have tried two different approaches to do the same
    > thing, i.e. flipping the bold format.
    >
    > Approach #1
    > Set rSel = Selection
    > rSel.Cells(1, 1).Font.Bold = Not rSel.Cells(1, 1).Font.Bold
    >
    > Approach #2
    > Set rSel = Selection
    > rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold = _
    > Not rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold
    >
    > Now, i'd say they are equivalent. However, what happens
    > makes me a little bit confused. When my selection starts
    > from A1, both approaches work in exactly the same way.
    > As soon as i start my selection from, say, B2, i get the
    > following difference.
    > Approach #1
    > The top, leftmost element of the selection (here it's B2)
    > gets changed, as expected to.
    > Approach #2
    > The _second_ top, leftmost element of the selection (here
    > it's C3) gets changed, to my astonishment.
    >
    > It wouldn't surprise me if it was a matter of defnition of
    > Range but as far as i can read the docs, there's nothing in
    > there regarding this behavior. Any comments?
    >
    >
    > --
    > Vänligen
    > Konrad
    > ---------------------------------------------------
    >
    > Sleep - thing used by ineffective people
    > as a substitute for coffee
    >
    > Ambition - a poor excuse for not having
    > enough sence to be lazy
    > ---------------------------------------------------
    >
    >


  5. #5
    Dave Peterson
    Guest

    Re: Range missing its target area weirdly

    Chip has some notes written by Alan Beban:
    http://www.cpearson.com/excel/cells.htm

    It may be an interesting read for you.



    Konrad Viltersten wrote:
    >
    > I have tried two different approaches to do the same
    > thing, i.e. flipping the bold format.
    >
    > Approach #1
    > Set rSel = Selection
    > rSel.Cells(1, 1).Font.Bold = Not rSel.Cells(1, 1).Font.Bold
    >
    > Approach #2
    > Set rSel = Selection
    > rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold = _
    > Not rSel.Range(rSel.Cells(1, 1), rSel.Cells(1, 1)).Font.Bold
    >
    > Now, i'd say they are equivalent. However, what happens
    > makes me a little bit confused. When my selection starts
    > from A1, both approaches work in exactly the same way.
    > As soon as i start my selection from, say, B2, i get the
    > following difference.
    > Approach #1
    > The top, leftmost element of the selection (here it's B2)
    > gets changed, as expected to.
    > Approach #2
    > The _second_ top, leftmost element of the selection (here
    > it's C3) gets changed, to my astonishment.
    >
    > It wouldn't surprise me if it was a matter of defnition of
    > Range but as far as i can read the docs, there's nothing in
    > there regarding this behavior. Any comments?
    >
    > --
    > Vänligen
    > Konrad
    > ---------------------------------------------------
    >
    > Sleep - thing used by ineffective people
    > as a substitute for coffee
    >
    > Ambition - a poor excuse for not having
    > enough sence to be lazy
    > ---------------------------------------------------


    --

    Dave Peterson

  6. #6
    Konrad Viltersten
    Guest

    Re: Range missing its target area weirdly

    > The Range method returns a relative reference. If unqualified it
    > defaults to the active sheet and references are relative to the
    > top-left cell - i.e. relative to cell A1 and Range("C3") refers to
    > cell C3. However, if you select cells F5:G6 and specify
    > Selection.Range("C3") it will return cell H7. In other words, "C3"
    > specifies an offset from the top-left corner of the selection (F5)
    > instead of from the top-left corner of the worksheet.



    Aha, got it. Thanks to all for the answers. Most helpfull.

    --
    Vänligen
    Konrad
    ---------------------------------------------------

    Sleep - thing used by ineffective people
    as a substitute for coffee

    Ambition - a poor excuse for not having
    enough sence to be lazy
    ---------------------------------------------------


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1