Should I be defining ranges differently?

[davidam]

Hello,
I am sure that there is no error in the following:

Dim Loc as Range
Set Loc = Range("worksheet!named_range").Find(What:=name)
Range(Loc.Offset(8001,0).Address  & ":" & Loc.Offset(9900,0).Address).Copy 
           mainSheet.Range("I201")

But the copy fails.

As a test I tried this:

mainSheet.Range("I201").Value = Loc.Offset(8001,0).Value

...and it works just fine. Which leads me to wonder if there is something inefficient about using the .Address method with a large range like this.
Thanks,
David

 

[RoryA]

In what way does it fail?
Address is unnecessary there:

Dim Loc as Range
Set Loc = Range("worksheet!named_range").Find(What:=name)
Range(Loc.Offset(8001,0), Loc.Offset(9900,0)).Copy mainSheet.Range("I201")

 

[davidam]

There is actually a series of ranges that are supposed to be copied and it simply posted the wrong data to one location and nothing to several others.

 

[Rick Rothstein]

Range(Loc.Offset(8001,0), Loc.Offset(9900,0)).Copy mainSheet.Range("I201")[/Code]

Or that line could written this way as well...

Loc.Offset(8001).Resize(1900).Copy mainSheet.Range("I201")

 

[davidam]

Well that does seem to work. I have always used .Address, and with good results...probably got from this site! Thank you!

 

[davidam]

Will try that too Rick...Thanks!