Is it possible to use variable in brackets when using .FormulaR1C1 ?

[ltirole]

Hello,
I´m new to VBA and I wonder if you can put a variable inside the brackets when using .FormulaR1C1.
Here is an example of what I´m trying to do:

Sub b()


    Dim fl As Integer
    Dim fc As Integer
    Dim nb_parts As Integer
    Dim nb_measurments As Integer
    fl = 8
    fc = 8
    nb_measurments = 7
    nb_parts = 30
    
    
    For x = 0 To nb_measurments - 1
    Cells(fl + nb_parts + 4, fc + x).FormulaR1C1 = "=AVERAGE(R[-(nb_parts)]C:R[-4]C)" 'here i get run time error 1004
    Next x
End Sub

Do you have a solution ?

 

[Fazza]

nb_parts is just part of what is a text string where you're getting the error. it makes no sense, but i guess you know that

a common approach is ="AVERAGE(R[-" & nb_parts & "]C:R[-4]C)"

another way would be to replace the string by its value
=REPLACE$("AVERAGE(R[-(nbparts)]C:R[-4]C)","nb_parts",nb_parts)

HTH

 

[ltirole]

Thank you very much for quick answer, it worked
Best Regards,
LT

 

[Fazza]