Macro to add a variable list

[Guest]

G'day all

I need a macro that adds (sums) a variable list of numbers.

The data could be
A3 40
A4 5
A5 19
A6 36

I'm after a macro that if the cursor is in B6, the total will be given of 100.

I move the cursor to another list (anywhere in the workbook) and it will
give the answer to that list.

Eg move cursor to B18, hit the macro which gives the answer (in B18) as 205.

A13 23
A14 4
A15 67
A16 52
A17 12
A18 47

So far I've come up with the following code;-

Dim MyRange As Range
ActiveCell.Offset(0, -1).Select
Set MyRange = Range(Selection, Selection.End(xlUp)).Select
ActiveCell.FormulaR1C1 = "=SUM([" & MyRange & "])"

But it gives the following error at the "Set MyRange" line
"Runtime Error 424 Object Required"

I also suspect that the "Activecell.formula" line is going to give me
problems as well.
Basically, I want to sum the range and that range will be of variable length.

Any suggestions???? The code doesn't have to follow what I've done. As long
as it works.

Thanks in advance

PS As most of you will have deduced by now, I know absolutely nothing about
VBA programming (- but I'm learning )

 

[Vasant Nanavati]

If it doesn't have to be a formula:

ActiveCell = WorksheetFunction.Sum(Range(ActiveCell.Offset(, -1),
ActiveCell.Offset(, -1).End(xlUp)))

 

[Guest]

Thanks alot - It worked!!!!!

If it doesn't have to be a formula:

ActiveCell = WorksheetFunction.Sum(Range(ActiveCell.Offset(, -1),
ActiveCell.Offset(, -1).End(xlUp)))

--

Vasant

G'day all

I need a macro that adds (sums) a variable list of numbers.

The data could be
A3 40
A4 5
A5 19
A6 36

I'm after a macro that if the cursor is in B6, the total will be given of
100.

I move the cursor to another list (anywhere in the workbook) and it will
give the answer to that list.

Eg move cursor to B18, hit the macro which gives the answer (in B18) as
205.

A13 23
A14 4
A15 67
A16 52
A17 12
A18 47

So far I've come up with the following code;-

Dim MyRange As Range
ActiveCell.Offset(0, -1).Select
Set MyRange = Range(Selection, Selection.End(xlUp)).Select
ActiveCell.FormulaR1C1 = "=SUM([" & MyRange & "])"

But it gives the following error at the "Set MyRange" line
"Runtime Error 424 Object Required"

I also suspect that the "Activecell.formula" line is going to give me
problems as well.
Basically, I want to sum the range and that range will be of variable
length.

Any suggestions???? The code doesn't have to follow what I've done. As
long
as it works.

Thanks in advance

PS As most of you will have deduced by now, I know absolutely nothing
about
VBA programming (- but I'm learning )