vbaexcelformulas

Change formula references during copy

I have a worksheet containing 35 columns of formulas which looks up data from another worksheet. Every time data is entered into the lookup worksheet ("Data") I want to add another set of formulas to the bottom of the row. I've managed to get the code to copy the correct formulas over these 35 columns, to the correct location at the bottom. But when they paste, the exact text of the formulas is copied, rather than changing the cells referenced.

e.g. formula "=OFFSET(Data!$B$1,(A7736-1)*34,0)" copied from row 7736 into row 8300 should become =OFFSET(Data!$B$1,(A8300-1)*34,0) but stays as "=OFFSET(Data!$B$1,(A7736-1)*34,0)"

This is my code

Dim workbk As Workbook
Dim databk As Workbook
Dim MRD As Worksheet
Dim formularrange As Range 
Dim lasttrayname As String   
Dim datarow As Long
Dim nrow As Long
Dim loopcount As Long
Dim urow As Long

Set workbk = ActiveWorkbook

'set Macro recording information sheet
Set MRD = Worksheets("Macro RunDate")

'find last date updated
urow = Worksheets("Macro RunDate").Range("A" & Rows.Count).End(xlUp).Row

'find last tray used
lasttray = MRD.Range("B" & urow).Value

'find row to paste raw tray data
datarow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row

    Do while *condition*

    '...More code here....

    Loopcount = 0
    Loopcount = loopcount + 34
    Loop

Go to Tidy:

 Tidy:

 If loopcount > 0 Then

'convert to correct number of rows to copy
 loopcount = (loopcount)/34*8

        'copy formulas in Sheet2 for correct number of trays     
        'determine end row by finding "lasttray"
        nrow = Worksheets("Sheet2").Range("A:A").Find(What:=lasttray, _
        SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row + 8


'repeat formulas
 Worksheets("Sheet2").Range(Cells(nrow + 1, "A"), Cells(nrow + loopcount + 1, _
 "S")).Formula = Worksheets("Sheet2").Range("A3975:s" & 3975 + loopcount).Formula

'calculate formula values
Worksheets("Sheet2").Range("A:E").Calculate

End If

So my question is, how do I copy these formulas and get the references to change?

Thanks in advance

Solution

  • Use .FormulaR1C1 rather than .Formula.

    – From the comment by @Rory Jun 4 '14 at 13:16