MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Passing a value to a range


Posted by John L. on January 27, 2001 9:46 AM

I have a worksheet with numeric values in cells B2 to B5

I am attempting to pass the values to range C2 to C5

Can anyone tell me why this command passes the values:
Range("C2:C5") = Range("B2:B5").Value

And this one does not:
Set myRange = Range("C2:C5")
myRange = Range("B2:B5").Value

Any help would be appreciated ... thanks.


Posted by Celia on January 27, 2001 4:51 PM

Can see no reason why it should not work.
(Unless you have Option Explicit at the top of your module, in which case you need to Dim myRange as Range)

Posted by Celia on January 27, 2001 4:56 PM

Can see no reason why it should not work.
(Unless you have Option Explicit at the top of your module, in which case you need to Dim myRange as Range)

Are you getting an error message?

Posted by John L. on January 29, 2001 10:27 PM

Celia, I found the answer...if I put .Value after
myRange ( myRange.Value ) it works.

The reason seems to be that you can not really pass
a value to an object, you must pass it to the objects
property. In this case the property is .value.

Posted by Celia on January 30, 2001 12:53 AM


Or if you Dim myRange as Range then you can pass the values to myRange with the code you posted. However, it is good practice to assign to an object the specific property being passed to it.