Objective
To create the sum formula using VBA excel.
Approach
In this VBA Excel automation, we have added the numeric values in column A. We have the numeric data from cell A2 to A6.Here we have used the formula property of range object. We have applied the formula in cell C2. Here formula can be seen in the formula bar as shown in the image below.
Code
We have initialized the variable CellRange, which contains the cell range which we want to add. Variable CellRange can be used to make the range more dynamic.
Sub SumFormula()
Dim CellRange As String
'Range where data exists
CellRange = "A2:A6"
'Apply the sum formula in cell C1
Range("C1").Formula = "=sum( " & CellRange & ")"
End Sub
In the below shown image we can see that sum formula is applied in cell C1 and result is shown in cell C1. We can see the applied formula/function in formula bar as shown below.
Note
- We can modify the cell range as per the requirement.
Reference
https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula
Post you may like
