Autosum in Macro

[Herman Merman]

I want to get a macro to auto sum a variable range of numbers in a column:

I have found the following code for this from www.info-tat.com/XLVBA.htm

vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = -vRowBottom - vRowTop + 1
Selection.FormulaR1C1="=SUM(R["& -vDiff& "]C:R[-1]C)"

End Sub


The last line produces a compile error "Expected End of Statement" which I
can't figure out, I suspect it's something to do with the brackets or
quotations used.

Anybody know what's wrong with this code or have a better piece of code that
will auto sum a column?

Thanks

 

[Frank Kabel]

Hi
can you explain with some more detail what you're trying to achieve
with your macro

 

[David McRitchie]

Herman is trying to generate the equivalent of
B12: =SUM(B$5:OFFSET(B12,-1,0))
when B12 is the active cell.

The above is much better than the SUM toolbar button
because it allows one to insert a row immediately above
the formula summing cells above. More information on
the use of OFFSET Worksheet Formula in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset

Frank's question about how this is supposed to be used
would be very relevant to the question, since you are
working with macro code. Also be aware of the differnce
between R1C1 addressing and A1 addressing.

The following is an Event macro and have used ActiveCell
instead of target so you can copy code and paste into
whatever macro you are using it in.
http://www.mvps.org/dmcritchie/excel/event.htm

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Cancel = True '-- Get out of Edit mode
ActiveCell.Formula = "=SUM(" & Cells(5, _
ActiveCell.Column).Address(1, 0) _
& ":OFFSET(" & ActiveCell.Address(0, 0) & ",-1,0))"
End Sub

You posted this for a macro solution, macro solutions
really should be posted in excel.programming

worksheet.functions is for the builtin Excel functions
(no programming code).

One big difference would be that a macro solution here
might generally tell the poster how to install and use a
macro http://www.mvps.org/dmcritchie/excel/getstarted.htm
since you are using macros, then the instruction how to
install and use is strictly for others reading your thread.

Also because of the request for macro, I am wondering
if you (Herman) know about the fill handle, so that you
can drag the fill handle across the row to put a SUM
(or other) formula into adjacent cells with addresses adjusted.
http://www.mvps.org/dmcritchie/excel/fillhand.htm

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi
can you explain with some more detail what you're trying to achieve
with your macro

--
Regards
Frank Kabel
Frankfurt, Germany

I want to get a macro to auto sum a variable range of numbers in a
column:

I have found the following code for this from
www.info-tat.com/XLVBA.htm

vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = -vRowBottom - vRowTop + 1
Selection.FormulaR1C1="=SUM(R["& -vDiff& "]C:R[-1]C)"

End Sub


The last line produces a compile error "Expected End of Statement"
which I can't figure out, I suspect it's something to do with the
brackets or quotations used.

Anybody know what's wrong with this code or have a better piece of
code that will auto sum a column?

Thanks

 

[Myrna Larson]

You have two problems here, syntax and arithmetic.

It's not with the brackets or the quotes. The syntax issue is with "-vDiff&".
As you wrote it, the ampersand is a type declaration character not an operator
(it's a holdover from earlier days -- please don't ask me to explain further).
You need a space between the f and the ampersand:

-vDiff &

After I fixed that problem, I selected cell A11, ran your code, and ended up
with a circular reference! The formula I got was =SUM(A10:A25). Your
arithmetic is wrong. It should be

vDiff = vRowBottom - vRowTop + 1

(no minus in front of vRowBottom)

When creating a formula becomes an exercise in concatenating strings and
getting the quotes in the right places, I write out the formula with a
place-holder for the number, verify that I have the syntax right, then replace
the place-holder with the number, like so:

With ActiveCell
If IsEmpty(.Offset(-2,0).Value) Then
vRowTop = .Row - 1
Else
vRowTop = .Offset(-1,0).End(xlUp).Row
End If
End With

vTopOffset = vRowTop - ActiveCell.Row 'result is negative

f = "=SUM(R[XX]C:R[-1]C)"
f = Replace(f, "XX", Format$(vTopOffset))
Selection.FormulaR1C1=f

 

[Herman Merman]

Hi Frank, The Macro is trying to put totals at the bottom of a column of
numbers in a worksheet.

Hi
can you explain with some more detail what you're trying to achieve
with your macro

--
Regards
Frank Kabel
Frankfurt, Germany

I want to get a macro to auto sum a variable range of numbers in a
column:

I have found the following code for this from
www.info-tat.com/XLVBA.htm

vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = -vRowBottom - vRowTop + 1
Selection.FormulaR1C1="=SUM(R["& -vDiff& "]C:R[-1]C)"

End Sub


The last line produces a compile error "Expected End of Statement"
which I can't figure out, I suspect it's something to do with the
brackets or quotations used.

Anybody know what's wrong with this code or have a better piece of
code that will auto sum a column?

Thanks

 

[Herman Merman]

Thanks Myrna, that worked a treat.

You have two problems here, syntax and arithmetic.

It's not with the brackets or the quotes. The syntax issue is with "-vDiff&".
As you wrote it, the ampersand is a type declaration character not an operator
(it's a holdover from earlier days -- please don't ask me to explain further).
You need a space between the f and the ampersand:

-vDiff &

After I fixed that problem, I selected cell A11, ran your code, and ended up
with a circular reference! The formula I got was =SUM(A10:A25). Your
arithmetic is wrong. It should be

vDiff = vRowBottom - vRowTop + 1

(no minus in front of vRowBottom)

When creating a formula becomes an exercise in concatenating strings and
getting the quotes in the right places, I write out the formula with a
place-holder for the number, verify that I have the syntax right, then replace
the place-holder with the number, like so:

With ActiveCell
If IsEmpty(.Offset(-2,0).Value) Then
vRowTop = .Row - 1
Else
vRowTop = .Offset(-1,0).End(xlUp).Row
End If
End With

vTopOffset = vRowTop - ActiveCell.Row 'result is negative

f = "=SUM(R[XX]C:R[-1]C)"
f = Replace(f, "XX", Format$(vTopOffset))
Selection.FormulaR1C1=f

I want to get a macro to auto sum a variable range of numbers in a column:

I have found the following code for this from www.info-tat.com/XLVBA.htm

vRowTop = 5
vRowBottom = ActiveCell.Offset(-1, 0).Row
vDiff = -vRowBottom - vRowTop + 1
Selection.FormulaR1C1="=SUM(R["& -vDiff& "]C:R[-1]C)"

End Sub


The last line produces a compile error "Expected End of Statement" which I
can't figure out, I suspect it's something to do with the brackets or
quotations used.

Anybody know what's wrong with this code or have a better piece of code that
will auto sum a column?

Thanks