Assign formula for each cell in a column?-CodePudding

I'm new in VBA. I would appreciate some help with advices about how to assign a formula for each cell in a table that changes its data daily.

Here is the table I need to update. What I need to do is calculating the goal for each day using the formula: =(K12-SUM(C2:C3))/(L13-1), where K12 equals to GOAL AG, minus the SUM of C2 till the current day, divided by the working days(L13) minus the day position (1 in this case since I need to start from 0). So basically I'd need to change the sum and the working days for each cell in column B to get daily goals. Is there a way to do this in VBA.

Note: The dates will be changing everyday so sometimes the table will have more days for calculating or less.

TablePic

This is what my code looks like. It calculates the goal for each day but I want to find a way to calculate the goal for each cell in a dynamical way, since everyday the dates will change:

'26
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=(R[10]C[10]-SUM([@[REAL AG ]]))/(R[11]C[11])"
    '27
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=(R[9]C[10]-SUM(R[-1]C[2]:RC[2]))/(R[10]C[11]-1)"
    '28
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=(R[8]C[10]-SUM(R[-2]C[2]:RC[2]))/(R[9]C[11]-2)"
    '29
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=(R[7]C[10]-SUM(R[-3]C[2]:RC[2]))/(R[8]C[11]-3)"
    '3
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=(R[6]C[10]-SUM(R[-4]C[2]:RC[2]))/(R[7]C[11]-4)"
    '4
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=(R[5]C[10]-SUM(R[-5]C[2]:RC[2]))/(R[6]C[11]-5)"
    '5
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=(R[4]C[10]-SUM(R[-6]C[2]:RC[2]))/(R[5]C[11]-6)"
    '6
     Range("B9").Select
    ActiveCell.FormulaR1C1 = "=(R[3]C[10]-SUM(R[-7]C[2]:RC[2]))/(R[4]C[11]-7)"
    '10 oct
     Range("B10").Select
    ActiveCell.FormulaR1C1 = "=(R[2]C[10]-SUM(R[-8]C[2]:RC[2]))/(R[3]C[11]-8)"
    '11
     Range("B11").Select
    ActiveCell.FormulaR1C1 = "=(R[1]C[10]-SUM(R[-9]C[2]:RC[2]))/(R[2]C[11]-9)"
    '12
     Range("B12").Select
    ActiveCell.FormulaR1C1 = "=(R[0]C[10]-SUM(R[-10]C[2]:RC[2]))/(R[1]C[11]-10)"

CodePudding user response：

Follow the below piece of VBA code;

Sub code()

Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & lRow).Formula = "=($K$12-SUM($C$2:C2))/($L$13-(ROW()-2))"

End Sub

Output;