Strange CopyRange behaviour

[Bruno Campanini]

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

 

[Paul Robinson]

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

 

[Dave Peterson]

I think it's just the prime example to specify the property that you want!

 

[eliano]

I think it's just the prime example to specify the property that you want!

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]

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.

I think it's just the prime example to specify the property that you want!

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

 

[eliano]

I don't know the reason, but I do know that specifying the properties youwant
and qualifying the ranges you're using are both good programming practices.

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

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]

I try to do that with my code.

On 09/18/2010 17:48, eliano wrote: