You can use the following basic syntax to calculate the sum of values in a range using VBA:
Sub SumValues()
Range("D2") = WorksheetFunction.Sum(Range("B2:B11"))
End Sub
This particular example calculates the sum of values in the range B2:B11 and assigns the result to cell D2.
If you would instead like to display the sum of values in a message box, you can use the following syntax:
Sub SumValues()
'Create variable to store sum of values
Dim sum As Single
'Calculate sum of values in range
sum = WorksheetFunction.Sum(Range("B2:B11"))
'Display the result
MsgBox "Sum of Values in Range: " & sum
End Sub
The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:
Example 1: Calculate Sum of Range Using VBA and Display Results in Cell
Suppose we would like to calculate the sum of values in the points column and output the results in a specific cell.
We can create the following macro to do so:
Sub SumValues()
Range("D2") = WorksheetFunction.Sum(Range("B2:B11"))
End Sub
When we run this macro, we receive the following output:
Notice that cell D2 contains a value of 245.
This tells us that the sum of values in the points column is 245.
Example 2: Calculate Sum of Range Using VBA and Display Results in Message Box
Suppose we would instead like to calculate the sum of values in the points column and output the results in a message box.
We can create the following macro to do so:
Sub SumValues()
'Create variable to store sum of values
Dim sum As Single
'Calculate sum of values in range
sum = WorksheetFunction.Sum(Range("B2:B11"))
'Display the result
MsgBox "Sum of Values in Range: " & sum
End Sub
When we run this macro, we receive the following output:
The message box tells us that the sum of values in the range B2:B11 is 245.
Note that in this example we calculated the sum of values in the range B2:B11.
However, if you’d like to instead calculate the sum of values in an entire column you could type B:B instead.
This will calculate the sum of values for every cell in column B.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
VBA: How to Write SUMIF and SUMIFS Functions
HI
I have a 30000 numbers in coulmn I and a number in L1.I want to find sum of which numbers is equal to L1.color those cells.
could you help me?
Yes! You can use VBA to find a subset of numbers in column **I** that sum up to the value in **L1**, and then color those cells.
This problem is known as the **Subset Sum Problem**, which is a combinatorial optimization problem. Given that you have 30,000 numbers, an **exhaustive search** may be slow, so I’ll provide an **efficient recursive approach** using VBA.
### Steps:
1. Read the target value from `L1`.
2. Iterate through column `I` to find a subset that sums up to `L1`.
3. If found, color the corresponding cells.
—
### VBA Code:
“`vba
Sub HighlightSubsetSum()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim numbers As Variant
Dim target As Double
Dim i As Long, j As Long
‘ Read target value from L1
target = ws.Range(“L1”).Value
‘ Read column I values
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “I”).End(xlUp).Row
numbers = ws.Range(“I1:I” & lastRow).Value
‘ Create an array to store indices of the subset
Dim selectedIndices() As Long
ReDim selectedIndices(1 To lastRow) ‘ Max size
‘ Try to find a subset sum
If FindSubset(numbers, target, selectedIndices, lastRow) Then
‘ Color the selected cells
For i = 1 To lastRow
If selectedIndices(i) = 1 Then
ws.Cells(i, 9).Interior.Color = RGB(255, 255, 0) ‘ Yellow
End If
Next i
MsgBox “Subset found and highlighted!”, vbInformation
Else
MsgBox “No subset found.”, vbExclamation
End If
End Sub
Function FindSubset(numbers As Variant, target As Double, selectedIndices() As Long, n As Long) As Boolean
Dim i As Long
‘ Use backtracking to find a subset
If target = 0 Then
FindSubset = True
Exit Function
End If
If n = 0 Or target < 0 Then FindSubset = False Exit Function End If ' Try including the last element selectedIndices(n) = 1 If FindSubset(numbers, target - numbers(n, 1), selectedIndices, n - 1) Then FindSubset = True Exit Function End If ' Exclude the last element and try again selectedIndices(n) = 0 If FindSubset(numbers, target, selectedIndices, n - 1) Then FindSubset = True Exit Function End If FindSubset = False End Function ``` --- ### Explanation: 1. The **`HighlightSubsetSum`** subroutine: - Reads the numbers from column `I` into an array. - Calls `FindSubset` to find a subset that sums to `L1`. - If found, it colors the corresponding cells in **yellow**. 2. The **`FindSubset`** function: - Uses **recursive backtracking** to check if a subset sums to `L1`. - Returns `True` if a valid subset is found. - Uses an array (`selectedIndices`) to track which numbers are included. --- ### How to Use: 1. Open Excel. 2. Press **ALT + F11** to open the VBA editor. 3. Go to **Insert > Module**.
4. Copy and paste the code.
5. Run `HighlightSubsetSum()`.
—
### Notes:
– This method is efficient for **small to medium datasets**.
– If you have **very large data**, you may need a more optimized approach using **dynamic programming**.
– If no exact match is found, the script will display `”No subset found.”`