Having an odd problem with the Range.Copy method.

This code:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy Destination:=target

works fine. However the same thing with positional parameter:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy (target)

fails with the error-

Run-time error '1004':
Copy methid of Raneg class failed

As far as I can see they mean exactly the same thing (source and target are
both declared as type Range). Any idea why this happens?

Regards; Colin

(target)

dereferences target and returns the value of target. Remove the parentheses
in accordance with VBA syntax. When calling and referencing, use parentheses
only when dictated by the syntax.

--
Regards,
Tom Ogilvy


"colin_e" wrote:

Having an odd problem with the Range.Copy method.

This code:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy Destination:=target

works fine. However the same thing with positional parameter:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy (target)

fails with the error-

Run-time error '1004':
Copy methid of Raneg class failed

As far as I can see they mean exactly the same thing (source and target are
both declared as type Range). Any idea why this happens?

Regards; Colin

Hmm, I hadn't realised was a () dereference operator, even in a subroutine or
function call. I'm relatively new to VBA, I've not seen this in other
languages.

It's confusing because the interactive editor offers up brackets around the
parameter when you are typing the function , kind-of encourages using the
brackets.

Anyway, thanks for the info, at least I now know what's going on.

Regards: Colin

"Tom Ogilvy" wrote:

(target)

dereferences target and returns the value of target. Remove the parentheses
in accordance with VBA syntax. When calling and referencing, use parentheses
only when dictated by the syntax.

--
Regards,
Tom Ogilvy


"colin_e" wrote:

Having an odd problem with the Range.Copy method.

This code:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy Destination:=target

works fine. However the same thing with positional parameter:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy (target)

fails with the error-

Run-time error '1004':
Copy methid of Raneg class failed

As far as I can see they mean exactly the same thing (source and target are
both declared as type Range). Any idea why this happens?

Regards; Colin

Hi colin_e,

You might find the issue with using the parenthesis easier to understand if
you apply associative thinking. For example, you could think of them as a
return envelope. So whenever your code reuires a return value, just INCLUDE
THE RETURN ENVELOPE.

To illustrate its use:
with MsgBox, to display a message to the user would be:

MsgBox "Hello!",... 'optional parameters as desired.

To evaluate a response from the user:

vVar = MsgBox("Do you want to continue?", vbYesNo,... 'optional parameters)

where vVar is a Variant type variable used to hold the returned value of the
button the user selected. To evaluate the user's choice:

If vVar = vbYes then... 'do something
-OR-
If Not vVar = vbYes then... 'do something

Using the Not operator, in this case, is the same as testing for vbNo. You
can also implement Select Case for more choices such as might be used on a
custom userform.

What's simple about associative thinking is it's (generally) easier to
process. In the case of the MsgBox example, the displayed message doesn't
include a "return envelope" because you're not expecting anything back.

Another example would be a function that requires arguments. It would be
done like this:

aFunction Arg1, Arg2...
where the function only needs the arguments to do its thing.

Where a function returns a value:

something = aFunction(Arg1, Arg2)
-OR-
If aFunction(Arg1, Arg2)...
-OR-
If Not aFunction(Arg1, Arg2)...

HTH

In regard to your .Copy destination... issue, -you could do this:

target = source


Regards,
GS


"colin_e" wrote:

Having an odd problem with the Range.Copy method.

This code:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy Destination:=target

works fine. However the same thing with positional parameter:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy (target)

fails with the error-

Run-time error '1004':
Copy methid of Raneg class failed

As far as I can see they mean exactly the same thing (source and target are
both declared as type Range). Any idea why this happens?

Regards; Colin

Just for clarification since the OP says he is new to VBE.

In regard to your .Copy destination... issue, -you could do this:

target = source

that would be equivalent to a pastespecial operation rather than a straight
Copy as the OP is doing.

Also, from the code, Target has been set to a single cell, but it I sense
the source is not a single cell.

The copy problem was a result of the parentheses and not a separate problem
from what I read.

--
Regards,
Tom Ogilvy



"GS" wrote in message
...
Hi colin_e,

You might find the issue with using the parenthesis easier to understand
if
you apply associative thinking. For example, you could think of them as a
return envelope. So whenever your code reuires a return value, just
INCLUDE
THE RETURN ENVELOPE.

To illustrate its use:
with MsgBox, to display a message to the user would be:

MsgBox "Hello!",... 'optional parameters as desired.

To evaluate a response from the user:

vVar = MsgBox("Do you want to continue?", vbYesNo,... 'optional
parameters)

where vVar is a Variant type variable used to hold the returned value of
the
button the user selected. To evaluate the user's choice:

If vVar = vbYes then... 'do something
-OR-
If Not vVar = vbYes then... 'do something

Using the Not operator, in this case, is the same as testing for vbNo. You
can also implement Select Case for more choices such as might be used on a
custom userform.

What's simple about associative thinking is it's (generally) easier to
process. In the case of the MsgBox example, the displayed message doesn't
include a "return envelope" because you're not expecting anything back.

Another example would be a function that requires arguments. It would be
done like this:

aFunction Arg1, Arg2...
where the function only needs the arguments to do its thing.

Where a function returns a value:

something = aFunction(Arg1, Arg2)
-OR-
If aFunction(Arg1, Arg2)...
-OR-
If Not aFunction(Arg1, Arg2)...

HTH

In regard to your .Copy destination... issue, -you could do this:

target = source


Regards,
GS


"colin_e" wrote:

Having an odd problem with the Range.Copy method.

This code:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy Destination:=target

works fine. However the same thing with positional parameter:

Set source= Range("VL_Source")
Set target = VL.Offset(1).Resize(1, 1)
source.Copy (target)

fails with the error-

Run-time error '1004':
Copy methid of Raneg class failed

As far as I can see they mean exactly the same thing (source and target
are
both declared as type Range). Any idea why this happens?

Regards; Colin

Hi Tom,

You raise a good point. Thanks for clarifying the difference!

Regards,
GS