Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
Range1 and Range2 are multiple-cells ranges.
1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
Hi
Range1 and Range2 are objects so Set Range2 = Range1 would be OK syntax, creating an object variable called Range2 but not overwriting Range2 values with Range1 values. Range2.Value = Range1 would not be good syntax as the output of the Value Method is being assigned to a range object. No overwrite will occur and there should be a datatype error?? Range2 = Range1.Value The default Method of the Range object is Value so this is the same as option 4. I know that the Value Method creates an array if the range is multi- celled, but not if it is a single cell (there is no 1 by 1 array). This must have a bearing on the issue I suppose and allow some kind of overloading of the Range datatype in this special case but my expertise ends there! regards Paul On Sep 16, 12:39*pm, "Bruno Campanini" wrote: Range1 and Range2 are multiple-cells ranges. 1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
I think it's just the prime example to specify the property that you want!
On 09/16/2010 06:39, Bruno Campanini wrote: Range1 and Range2 are multiple-cells ranges. 1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
On 17 Set, 01:13, Dave Peterson wrote:
I think it's just the prime example to specify the property that you want! On 09/16/2010 06:39, Bruno Campanini wrote: Range1 and Range2 are multiple-cells ranges. 1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno -- Dave Peterson- Nascondi testo citato - Mostra testo citato - Sorry Dave, but with the first example seems that I cannot have any copy. Public Sub test1() Dim rng1 As Excel.Range Dim rng2 As Excel.Range Set rng1 = [a1:a5] Set rng2 = [b1:b5] rng1.Value = 1 rng2 = rng1 'First example by Mr.Campanini End Sub and I cannot understand the reason. Regards Eliano |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
I don't know the reason, but I do know that specifying the properties you want
and qualifying the ranges you're using are both good programming practices. On 09/17/2010 18:08, eliano wrote: On 17 Set, 01:13, Dave wrote: I think it's just the prime example to specify the property that you want! On 09/16/2010 06:39, Bruno Campanini wrote: Range1 and Range2 are multiple-cells ranges. 1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno -- Dave Peterson- Nascondi testo citato - Mostra testo citato - Sorry Dave, but with the first example seems that I cannot have any copy. Public Sub test1() Dim rng1 As Excel.Range Dim rng2 As Excel.Range Set rng1 = [a1:a5] Set rng2 = [b1:b5] rng1.Value = 1 rng2 = rng1 'First example by Mr.Campanini End Sub and I cannot understand the reason. Regards Eliano -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
On 18 Set, 02:14, Dave Peterson wrote:
I don't know the reason, but I do know that specifying the properties you want and qualifying the ranges you're using are both good programming practices. On 09/17/2010 18:08, eliano wrote: On 17 Set, 01:13, Dave *wrote: I think it's just the prime example to specify the property that you want! On 09/16/2010 06:39, Bruno Campanini wrote: Range1 and Range2 are multiple-cells ranges. 1 - Range2 = Range1 2 - Range2.Value = Range1 3 - Range2 = Range1.Value 4 - Range2.Value = Range1.Value Lines 1 and 2 don't copy anything Lines 3 and 4 copy Range1 over Range2 Instead, if Range1 and Range2 are single-cell ranges, all lines 1 to 4 copy Range1 over Range2. That's since Excel 2003 (at least!) Any comments? Bruno -- Dave Peterson- Nascondi testo citato - Mostra testo citato - Sorry Dave, but with the first example seems that I cannot have any copy. Public Sub test1() Dim rng1 As Excel.Range Dim rng2 As Excel.Range Set rng1 = [a1:a5] Set rng2 = [b1:b5] rng1.Value = 1 rng2 = rng1 'First example by Mr.Campanini End Sub and I cannot *understand the reason. Regards Eliano -- Dave Peterson- Nascondi testo citato - Mostra testo citato - Thanks Dave. This means that even if the "Range" default should be "Value", we must always specify for a "Range" the property as "Value", both singlecell and multicell, in order to avoid any problem. Regards Eliano |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange CopyRange behaviour
I try to do that with my code.
On 09/18/2010 17:48, eliano wrote: <<snipped - Mostra testo citato - Thanks Dave. This means that even if the "Range" default should be "Value", we must always specify for a "Range" the property as "Value", both singlecell and multicell, in order to avoid any problem. Regards Eliano -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behaviour | Excel Programming | |||
strange behaviour | Excel Programming | |||
Strange if(***) behaviour? | Excel Discussion (Misc queries) | |||
Strange VBA Behaviour | Excel Programming | |||
Strange behaviour | Excel Worksheet Functions |