CODEWITHSUNDEEP
vbaexcel
otus
otus

Reputation: 385

Add formula to a cell VBA

I'm basically trying to programmatically add formulas to some cells of an excel worksheet, using the following code : 

'Ajout des formules de calcul de TD LABO
 For i = 20 To 22
    'Formule : TD=(2/3)*C2H6+C3H8+(4/3)*(i-C4H10+n-C4H10)
    newWorkBook.Worksheets("Feuil1").Cells(i, 16).Formula = (2 / 3) * Cells(i, 11) + Cells(i, 14) + (4 / 3) * (Cells(i, 6) + Cells(i, 7))
    'Calcul avec une décimale
    newWorkBook.Worksheets("Feuil1").Cells(i, 16).NumberFormat = "0.0"
 Next i

The problem is that the values of the cells were I added the formula don't change when I change the value of the cells used in the formulas' calculus.

Upvotes: 0

Views: 267

Answers (2)

Punith Gubbi
Punith Gubbi

Reputation: 692

Try This,

    Sub fmula()
        For i = 20 To 22
            Range("P" & i).Formula = "= (2/3)*(K" & i & ")+(4/3)*((F" & i & ")+(G" & i & "))"
        Next
    End Sub

Upvotes: 1

Rory
Rory

Reputation: 34075

You're not adding a formula, you're calculating a value and putting that in the cell. You need to use:

 For i = 20 To 22
    'Formule : TD=(2/3)*C2H6+C3H8+(4/3)*(i-C4H10+n-C4H10)
    newWorkBook.Worksheets("Feuil1").Cells(i, 16).FormulaR1C1 = "=(2/3)*RC11 +RC14+(4/3)*(RC6+RC7)"
    'Calcul avec une décimale
    newWorkBook.Worksheets("Feuil1").Cells(i, 16).NumberFormat = "0.0"
 Next i

or without looping:

'Formule : TD=(2/3)*C2H6+C3H8+(4/3)*(i-C4H10+n-C4H10)
With newWorkBook.Worksheets("Feuil1").Cells(22, 16).Resize(2)
    .FormulaR1C1 = "=(2/3)*RC11 +RC14+(4/3)*(RC6+RC7)"
    'Calcul avec une décimale
    .NumberFormat = "0.0"
End With

Upvotes: 3

Related Questions