Range missing its target area weirdly

[Chip Pearson]

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

 

[Chip Pearson]

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

 

[Konrad Viltersten]

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
---------------------------------------------------

 

[Greg Wilson]

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

 

[Dave Peterson]

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]

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
---------------------------------------------------