Update multiple formulas with VBA

[svjensen]

I need to update the formulas in a number of cells (range("C12:G14")) using VBA. The formula is fairly simple, as it is just a reference to an other cell times a given value (along the lines of '=C5*Sheet2!A$1$').

I have tried doing this with the following script, but the column reference is causing me some trouble.

For c = 2 To 7
    For r = 12 To 14
        Workbooks("Mappe2").Worksheets("Ark1").Cells(r, c).Formula = "=" & c & (r-7) & "*Sheet1!$A$1"
    Next r
Next c

 

[DonkeyOte]

Do you perhaps mean to do something along the lines of:

With Workbooks("Mapp2") 'no extension ?
    With .Worksheets("Ark1")
        .Range(.Cells(12, 2), .Cells(14, 7)).FormulaR1C1 = "=R[-7]C*Sheet1!R1C1"""
    End With
End With

 

[svjensen]

The missing extension was a typo on my part.

And yes, that is exactly was I was looking for. I am yet to become completely familiar with the R1C1 construct

Thanks