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

Answer 1

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