1. Computing

Excel VBA Example: Sum Cell Values Using a Selection Key

Answering a question from an About VB Reader

By

Updated July 05, 2007

"Nor Azita" wrote to ask how to calculate a total quantity for all cells in one column of a spreadsheet where a key for the intersecting row was equal to a selected cell. Communicating the question in cases like this is difficult. (If you're writing Excel VBA macros for customers, that makes getting clear and complete specifications just that much more important.) So I might not have answered exactly the right question. My interpretation of the question is probably best illustrated by the answer I coded:

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

In the example, Code10 is selected in column A and the CommandButton control is clicked. The VBA macro attached to the CommandButton Click event searches all the code values present in column A and when it finds one, it adds the corresponding value to a total that is displayed, along with the selected code, below the button. The result is:

220 + 1100 + 220 or 1540

If you're unfamiliar with Excel VBA macros, you may not be aware that you can even add controls to a spreadsheet like the CommandButton and Label controls shown. It's easy to draw them on a worksheet using the toolbar displayed using the menu options: View > Toolbars > Control Toolbox. Or just right-click on the menu bar to display the same list. These controls behave very much like VB 6 controls. In fact, if you have some experience coding VB 6, the VBA syntax is identical. But the Excel objects might take some study.

The entire program is in the CommandButton1_Click event subroutine. Here's what that looks like:

Private Sub CommandButton1_Click()
   Dim bottomCell As Range
   Dim currentCell As Range
   lblCodeSelected.Caption = ActiveCell.Value
   Set bottomCell = Range("A2").End(xlDown)
   Dim I As Integer
   I = 2
   Dim quantityForThisCode As Integer
   quantityForThisCode = 0
   Set currentCell = Range("A2")
   Do While I <= bottomCell.Row
      If currentCell = ActiveCell Then
         quantityForThisCode = quantityForThisCode + Range("B" & I).Value
      End If
      I = I + 1
      Set currentCell = Range("A" & I)
   Loop
   lblQuantity.Caption = quantityForThisCode
End Sub

The first two statements declare Range variables for use later. Range is the workhorse of Excel and it's just what it sounds like - a subarea of a worksheet. But since most of the data you're dealing with will be parts of a spreadsheet, Range is the common type that you normally use to refer to them.

Stashing the value in the cell that the user has clicked on into a label is an important usability step. Many users will fail to realize that they have to select a cell before clicking the button. This reminds them that you get what you ask for.

I then find the last cell in column A using the End method of the Range. The xlDown parameter performs the same function as Ctrl-Shift-Right Arrow so you won't be supprised to discover that you can also use xlUp, xlToRight, and xlToLeft. The selection is terminated by the edge of the worksheet or a blank cell. Cell A2, column A and column B are "magic constants" and the program fails if these parts of the spreadsheet are changed.

The Do While loop is pretty standard.

Do While I <= bottomCell.Row

Using the I counter to modify a Range selection, I can examine each cell until I reach the the bottom of the data in the column and add the corresponding value in column B to a variable.

  1. About.com
  2. Computing
  3. Visual Basic

©2014 About.com. All rights reserved.