vba copying ranges

[Giordano Bruno]

Hi Everyone. When Worksheets("Item") is active, I can use either of the following code lines

Worksheets("Item").Range("TEST").Formula = Worksheets("Model").Range(Cells(101, 22), Cells(101, 34)).Formula
Worksheets("Item").Range("TEST").Formula = Worksheets("Model").Range("V100:AH100").Formula

and they both do the same thing, but when I add "R1C1", i.e.

Worksheets("Item").Range("TEST").FormulaR1C1 = Worksheets("Model").Range(Cells(101, 22), Cells(101, 34)).FormulaR1C1
Worksheets("Item").Range("TEST").FormulaR1C1 = Worksheets("Model").Range("V100:AH100").FormulaR1C1

the first line fails with the message "Application-defined or object-defined error, although the second line works perfectly.
Both lines run perfectly if Worksheets("Model") is the active sheet, but I don't want to change worksheets.
I can work around the problem, but I'm curious to know why there is ever a difference between Range(Cells(101, 22), Cells(101, 34)).FormulaR1C1 and Range("V100:AH100").FormulaR1C1

 

[Peter_SSs]

why there is ever a difference between Range(Cells(101, 22), Cells(101, 34)).FormulaR1C1 and Range("V100:AH100").FormulaR1C1

It is not so much a difference between the ranges as expressed there but between the ranges expressed like this

Worksheets("Model").Range(Cells(101, 22), Cells(101, 34))
Worksheets("Model").Range("V100:AH100")

The second clearly refers to a range on the "Model" worksheet.

However, in the first line, Cells(101,22) and Cells(101,34) are not qualified to any worksheet and so will actually reference whatever the active sheet is. If the active sheet is "Test" then the first line above is trying to describe a range on "Model" starting at V101 on "Test" and ending at AH101 on "Test" which is clearly not possible.

Try qualifying all ranges ..
Worksheets("Model").Range(Worksheets("Model").Cells(101, 22), Worksheets("Model").Cells(101, 34))

 

[yky]

I think you need to qualify Cells, something like:

Worksheets("Model").Range(Worksheet("Model").Cells(101, 22), Worksheets("Model").Cells(101, 34)).FormulaR1C1

 

[Giordano Bruno]

Hi Peter and yky,
Thanks you both for the explanation. I had figured that the first reference was sufficient. I still do not understand why is works until I add R1C1, but I can confirm that my initial problem has been solved and the code now runs perfectly Your help is very much appreciated.
I have marked Peter's solution as solved since it arrived just before yours yky

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.