I want to use Sum,division and Multiplication in the particular cell through VBA.

[Shailender]

Hi folks, need a help on the above table. Every month one new column is going to inserted before the G column. I need a formula in I2 from A2 to sum of the columns inserted before G. The formula should do sum divided by column value with division of 12 months. Here is the attached Excel sheet for your reference.
Please refer to the cell I2 for more clarification.

 

[YasserKhalil]

May be

Sub Test()
    With Range("A1").CurrentRegion
        .Cells(1, .Columns.Count + 1).Value = "Header"
        .Cells(2, .Columns.Count + 1).Formula = "=SUM(A2:" & .Cells(2, .Columns.Count).Address(0, 0) & ")"
        .Cells(2, .Columns.Count + 3).Formula = "=SUM(A2:" & .Cells(2, .Columns.Count).Address(0, 0) & ")/" & .Columns.Count + 1 & "*12"
    End With
End Sub

 

[Shailender]

Hi, Yasserkhalil, thank you for quick reply. It is working. Need one more help could you please explain the coding what was written exactly...? Since i am new to VBA need little bit of explanation. ty.

 

[YasserKhalil]

Glad it helps you
In first line , it refers to the current region of cell A1 to get use of the columns count property ..
In second line the word Cells used to refer to specific cell .. in the second line it refers to first row and the column after the last column of the current region ..at last put the string "Header" in that cell

In the third line , it refers to the second row of the column after the last column and put the formula for summing

In the fourth and last line , it refers to the cell in the second row and the third column after the last column of the current region ..and put your formula after editing the address used to suit the current region
Hope this helps you ..
Regards

 

[Shailender]

Hi, Yasserkhalil, thank you for your reply. Need some modifications, Please find the attached excel sheet, the formula need to be inserted in AN2.

Thank you.

 

[Shailender]

Hey, Yasserkhalil, could you please help me out the same scenario in the latest sheet which i had attached yesterday..?