Why does define range with cell.address work but not cell only Excel VBA?

Question

I'm trying to work out how to apply a formula to a range of cells that is defined based on the Range.End method explained here.

I've avoided Select etc. as per How to avoid using Select in Excel VBA macros.

I've used the code below earlier on with regards to Range(Cells, Cells) and I had the desired output of copying an unknown number of rows dataset in column B related to a particular search entry for data in column A.

Set CopyFrom1 = Cells(FirstRow, "B")    
Set CopyFrom2 = Cells(LastRow, "B")

sourceBook.Sheets("Sheet1").Range(CopyFrom1, CopyFrom2).Copy _
    destBook.Sheets("Sheet1").Range("C6")

Now the dataset is copied across to destBook, I want to apply a formula to it. However, I don't know how many rows it is, so I use the Range(Cells, Cells) method again.

LRow = destBook.Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
Set LRowA = Cells(LRow, "D")
Set FRowA = Cells(6, "D")
destBook.Sheets("Sheet1").Range(LRowA, FRowA).Formula = "=C6*2.236936"

This resulted in: run-time error '1004': Application-defined or object-defined error.

I tried this method of qualifying Cells Why does Range work, but not Cells?, since the VBA code is running from the sourceBook and I'm working in destBook (different Workbook). However, I get run-time error '438': Object doesn't support this property or method. I don't quite understand what's different here in comparison to what I'm trying to achieve.

Just before posting this, I found https://stackoverflow.com/a/30030763/7199404, which gave me the result I needed. Code below:

destBook.Sheets("Sheet1").Range(LRowA.Address, FRowA.Address).Formula = "=C6*2.236936"

Could someone please explain to me why this works and the others do not?

Cells(LRow, "D") and Cells(6, "D") are implicitly referring to the active worksheet. Hence you can only use them to define a range on the active worksheet. If that's not destBook.Sheets("Sheet1"), then things go boom. Which part is unclear? — Mathieu Guindon
OK, I understand now how to explicitly define the object. For instance, destBook.Sheets("Sheet1").Cells(LRow, "D"). Why does the last line above work though with .Address? Is it doing the same thing or something different? — Huzaifa Essajee
After your clarification and reviewing link again, I now understand what the answer is trying to describe. — Huzaifa Essajee

Slai Slai · Accepted Answer · 2016-11-24T13:35:05

Saving the range can make it easier:

Set rngFrom = sourceBook.Sheets("Sheet1").Range("B" & FirstRow, "B" & LastRow)
Set rngTo = destBook.Sheets("Sheet1").Range("C6").Resize(rngFrom.Rows.Count)

rngTo.Value = rngFrom.Value
rngTo.Offset(, 1).Formula = "=C6*2.236936"

Why does define range with cell.address work but not cell only Excel VBA?

2 Answers