Copy relative formula in excel with VBA

Question

I'm trying to copy formulas with relative row and column names.

I have base column with 10 formulas and I'm trying to copy formula 5 times on columns on the right.

So for example in base column i have this formula =A1+B1 and i want to make next cell this =B1+C1 next =C1+D1 and so on.

I have this code

For i = 1 To Range("B1").Value
        For j = 1 To 10
            Sheet2.Cells(5 + j, 2 + i).FormulaR1C1 = "=c[-1]"
        Next j
Next i

But this just copies value from left cell

It would be easy if the formulas were same but they are different. So im trying to find a way to dynamically reference always left cell.

This is one of the real formulas

='Balance sheet'!B13*360/'P&L'!B2

I want next cell to be

='Balance sheet'!C13*360/'P&L'!C2

And with code i have now next cell is =B3

If =A1+B1, =B1+C1 and =C1+D1 are entered in three adjacent horizontal cells, it is the same formula as far as Excel is concerned (it looks the same in the R1C1 notation). Please give more details about your sheet layout. — GSerg
@ScottCraner Im trying to make formulas dynamic. That is not only formula i have 10 different formulas and they are presented in base column. — madeye
@GSerg I have updated question and put real formula. Take a note that this is only one of 10 other and in all i just want cell reference to be shifted one place right and that is it. — madeye
='Balance sheet'!B13*360/'P&L'!B2 and ='Balance sheet'!C13*360/'P&L'!C2 is the same formula if they are in horizontally adjacent cells @housefrommars. To copy this formula to the right you assign NewCell.FormulaR1C1 = OldCell.FormulaR1C1. — GSerg

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2015-09-22T18:56:02

If you want to copy a formula to right, that can be done with a fill operation. So with your formula in a cell defined by your base row and base column, and with it using relative addressing as in your question, merely:

Option Explicit
Sub foo()
    Const lBaseColumn As Long = 3
    Const lBaseRow As Long = 6
    Const lRpts As Long = 10

Cells(lBaseRow, lBaseColumn).Resize(columnsize:=lRpts).FillRight

End Sub

You should note that you can FillRight from multiple rows at once, if that is what you need to do. The below would fill C6:C10 through to L6:L10

Option Explicit
Sub foo()
    Const lBaseColumn As Long = 3
    Const lBaseRow As Long = 6
    Const lColRpts As Long = 10
    Const lRows As Long = 5

Cells(lBaseRow, lBaseColumn).Resize(rowsize:=lRows, columnsize:=lColRpts).FillRight

End Sub

Copy relative formula in excel with VBA

2 Answers