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?
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"