the "=RC[-6]" is a relative reference to cell C14 if the active cell is I14. The R without a bracketed number indicates to the formula to use the same row and the C[-6] indicates to use the column 6 to the left (left indicated by the minus sign. Plus sign would indicate to the right).
As for the loop, you don't need one. If your data is contiguous you can use column H to indicated how far down to paste your formulas. You can use something like this:
Dim r as Long
r = Range("H15",Cells(Rows.Count,8).End(xlUp)).Rows.Count
Selection.AutoFill Destination:=Range("I14:P" & r), Type:=xlFillDefault
to replace the code:
Selection.AutoFill Destination:=Range("I14:P65"), Type:=xlFillDefault

Originally Posted by
Aaron1978
Hi All,
I have the following subroutine from a recorded macro:
Sub GenerateCorrosionProfile()
Range("I14").Select
ActiveCell.FormulaR1C1 = "=RC[-6]" 'Writes =C14 to I14
Range("J14").Select
ActiveCell.FormulaR1C1 = "=R10C4" 'Writes =$D$10 to J14
Range("K14").Select
ActiveCell.FormulaR1C1 = "=RC[-8]" 'Writes =C14 to K14
Range("L14").Select
ActiveCell.FormulaR1C1 = "=RC[-5]" 'Writes =G14 to L14
Range("M14").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-5]" 'Writes =K14+H14 to M14
Range("N14").Select
ActiveCell.FormulaR1C1 = "=RC[-7]" 'Writes =G14 to N14
Range("O14").Select
ActiveCell.FormulaR1C1 = "=RC[-4]+RC[-7]" 'Writes =K14+H14 to O14
Range("P14").Select
ActiveCell.FormulaR1C1 = "=R10C4" 'Writes =$D$10 to P14
Range("I14:P14").Select
Selection.AutoFill Destination:=Range("I14:P65"), Type:=xlFillDefault
Range("I14:P65").Select
Range("I65").Select
End Sub
It's doing nothing more than copying data to cells in I14 to P14 and then pasting (autofill) all the data in I14:P14 to row 65 (see screen capture of the spreadsheet below to get a better understanding).
Attachment 5332
I would like to remove the restriction of only pasting to the 65th row and have a loop that would allow the user to paste to as many rows as is required until there is no more data to perform an operation on. i.e. make the loop stop once a blank cell is reached in =RC[-6].
Side note: The macro is using the term =RC[-6] for =C14. What's going on there then?
Best Regards,
Aaron
Bookmarks