Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Bob has a need to use the SUM function in a macro in order to find the sum of all the values in a column. The problem is that the number of cells to be summed will vary; for one run of the macro it could be 100 cells, while on the next it could be 300 and on the third only 25.

First, it is easy to use most worksheet functions (such as SUM) from within a macro. All you need to do is to preface the function name with "Application.WorksheetFunction." or simply "WorksheetFunction." Thus, if you know that each run of the macro will require summing A1:A100, then A1:A300, and finally A1:A25, you could use a macro like this:

Public Sub Sum_Demo() Dim myRange Dim Results Dim Run As Long For Run = 1 To 3 Select Case Run Case 1 myRange = Worksheets("Sheet1").Range("A1", "A100") Case 2 myRange = Worksheets("Sheet1").Range("A1", "A300") Case 3 myRange = Worksheets("Sheet1").Range("A1", "A25") End Select Results = WorksheetFunction.Sum(myRange) Range("B" & Run) = Results Next Run End Sub

This macro uses a For . . . Next loop to specify different ranges of cells to be summed. It then uses the SUM worksheet function to assign the sum to the Results variable, which is (finally) stuffed into a cell in column B. The results of the first run are put in B1, the second in B2, and the third in B3.

While this particular macro may not be that useful, it shows several helpful techniques, such as how to define a named range, how to use the SUM function, and how to stuff the sum into a cell. What the macro doesn't do is to show how to select a variable number of cells to be summed. To do this, it is best to rely upon the End method of the Range object. The following code line shows how you can stuff the sum of the range starting at A1 and extending to just before the first blank cell in the column:

myRange = ActiveSheet.Range("A1", Range("A1").End(xlDown)) Range("B1") = WorksheetFunction.Sum(myRange)

Note that a range (myRange) is defined as beginning with A1 and extending through whatever the End method returns. This is then summed and stuffed into B1.

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (3217) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using SUM In a Macro.

*Related Tips:*

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

CAN I HAVE IN VBA RANGE.VALUE=RANGE1.VALUE+RANGE2.VALUE

THAT IS

A1:A3 = 1 1 1

B1:B3 = 1 1 1

C1:C3 = 2 2 2

WITHOUT FOR ETC

EXCELLENT WORK ALTHOUGH

THAT IS

A1:A3 = 1 1 1

B1:B3 = 1 1 1

C1:C3 = 2 2 2

WITHOUT FOR ETC

EXCELLENT WORK ALTHOUGH

@Endy

Try the following code:

Dim i As Integer

Dim irow As Integer

irow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To irow

Cells(i, 1) = Cells(i, 1) * 100 / 114

Next i

You will need to add a command button and assign the macro to it. It is'nt the refresh button but it's still only one click.

If you don't like Cells(i, 1), you can always substitute it with Range("A" & i) if you prefer.

Try the following code:

Dim i As Integer

Dim irow As Integer

irow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To irow

Cells(i, 1) = Cells(i, 1) * 100 / 114

Next i

You will need to add a command button and assign the macro to it. It is'nt the refresh button but it's still only one click.

If you don't like Cells(i, 1), you can always substitute it with Range("A" & i) if you prefer.

Hi there,

Is there a way to write a macro that can calculate columns in this order:

Column A (Price * 100 / 114), so whenever i open the sheet and click refresh it automatically calculate all values in Column A, multiply the values found in Column A by 100 and divide it by 114?

Is there a way to write a macro that can calculate columns in this order:

Column A (Price * 100 / 114), so whenever i open the sheet and click refresh it automatically calculate all values in Column A, multiply the values found in Column A by 100 and divide it by 114?

... or this if you prefer!

Dim r As Integer

r = Range("B1").Value

Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & r))

Dim r As Integer

r = Range("B1").Value

Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & r))

@ ulysis

Here are three lines of code that should do the trick. What the code does is:

a) define an integer variable

b) assign the value of cell B1 to the variable

c) SUM the contents of column A from A1 to the selected row and put the answer into the cell of your choice (in this case B3)

Dim r As Integer

r = Range("B1").Value

Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & Range("B1").Value))

Here are three lines of code that should do the trick. What the code does is:

a) define an integer variable

b) assign the value of cell B1 to the variable

c) SUM the contents of column A from A1 to the selected row and put the answer into the cell of your choice (in this case B3)

Dim r As Integer

r = Range("B1").Value

Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & Range("B1").Value))

thanks for your useful tips..

but, i need vba to sumup certain rows based on a cell value

i.e. if cell b1 contains 6 then i want vba to sum cell a1 to a6 and if cell b1 contains 9 then vba to sum cell a1 to a9.

please help me on this

thanks in advance

but, i need vba to sumup certain rows based on a cell value

i.e. if cell b1 contains 6 then i want vba to sum cell a1 to a6 and if cell b1 contains 9 then vba to sum cell a1 to a9.

please help me on this

thanks in advance

Holy wow Dave, talk about being the exact right thing I needed. Thanks so much man.

Thank you so much Dave for your helpful comment

I just needed a small sample about makro programming. So thank you for this helpful, short and compact example. very good!

Here's an alternative if you want to add SUM to a cell when you don't know how many rows of data your macro will meet during any given run.

Assuming you want to add the values in Column D of your data, and put the total in the next row below, use the following two lines of code:

Range("D" & (x + 1)).Select

ActiveCell.FormulaR1C1 = "=SUM(R[-" & knt & "]C:R[-1]C)"

This may look a little complicated to anyone not familiar with macros, so let's break it down into its components to make it easier to understand.

Variables x and knt are Integer (or Long if you prefer).

x is the loop counter, so it holds the current Row number.

knt is a count of the number of rows of data processed, which allows for your data not starting at Row 1. You must choose your own method of assigning a value to knt.

The formula uses the R1C1 convention to define the range to be summed. The range is defined as the current Row (R) minus the number of records processed for the current Column (C), down to the current Row (R) minus 1 for the current Column (C).

In other words, it starts at the selected cell and sums the cells from the one immediately above back up along the column for as many rows as were processed.

If you prefer to have a blank row between the data and the SUM cell, then select the cell given by (x+2) and change the portion of the SUM formula to R[-2] to match.

After the macro has run, you will see the SUM formula in the usual format in the cell, such as =SUM(D4:D127) .

This works nicely for me. I hope it helps you too.

Assuming you want to add the values in Column D of your data, and put the total in the next row below, use the following two lines of code:

Range("D" & (x + 1)).Select

ActiveCell.FormulaR1C1 = "=SUM(R[-" & knt & "]C:R[-1]C)"

This may look a little complicated to anyone not familiar with macros, so let's break it down into its components to make it easier to understand.

Variables x and knt are Integer (or Long if you prefer).

x is the loop counter, so it holds the current Row number.

knt is a count of the number of rows of data processed, which allows for your data not starting at Row 1. You must choose your own method of assigning a value to knt.

The formula uses the R1C1 convention to define the range to be summed. The range is defined as the current Row (R) minus the number of records processed for the current Column (C), down to the current Row (R) minus 1 for the current Column (C).

In other words, it starts at the selected cell and sums the cells from the one immediately above back up along the column for as many rows as were processed.

If you prefer to have a blank row between the data and the SUM cell, then select the cell given by (x+2) and change the portion of the SUM formula to R[-2] to match.

After the macro has run, you will see the SUM formula in the usual format in the cell, such as =SUM(D4:D127) .

This works nicely for me. I hope it helps you too.