Sum Function in VBA Excel | debugvba.com

sum function vba excel

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.

 

sum function vba excel


Note

  1. 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

Apply vlookup formula in VBA Excel