| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 --------------------------------------------------- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding area codes only to phone numbers missing an area code | Excel Discussion (Misc queries) | |||
| Target area in Excel chart with different color | Charts and Charting in Excel | |||
| Target As Excel.Range or Target As Range | Excel Programming | |||
| Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range) | Excel Programming | |||
| "find" and "filter" working weirdly | Excel Discussion (Misc queries) | |||
Linear Mode
