Visual Basic Code

[Mike]

Hi All,

I want to write a macro that totals the values in consecutive rows in a
column in an Excel Spreadsheet.
The start and end rows will alter so I will have to use variables to define
the starting and the finishing cell.
Can anyone help me write a formula that will do this,

Thanks Mike.

 

[Bob Phillips]

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
Cells(iLastRow+1,"A").Formula = "=SUM(A" & _
Range("A1").End(xlDown) & ":A" & iLastRow & ")"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Art®]

I think you'll find it works better when you define the variable iLastRow
and to be on the safe side make sure you give the macro a name and an End
Sub


Sub AddColumn()

Dim iLastRow

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(iLastRow + 1, "A").Formula = "=SUM(A" & _
Range("A1").End(xlDown) & ":A" & iLastRow & ")"

End Sub

______________________

 

[Don Guillett]

You don't give a lot of detail and it sounds like homework but try this idea

Sub sumvarrows()
ac = 9
fr = 2 'or inputbox("Enter first row")
lr = Cells(Rows.Count, ac).End(xlUp).Row
MsgBox Application.Sum(Range(Cells(fr, ac), Cells(lr, ac)))
End Sub

 

[Bob Phillips]

It works just as well without defining it. It only doesn't work if you have
Option Explicit, and then you know quite quickly. If you are going top
declare it, at least give it a proper type (Long).

You also assume there isn't already a macro to embed this in, I didn't, but
it might help if you gave the macro a meaningful name rather than a totally
irrelevant name.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Mike]

Thanks for your help, all sorted now.

You don't give a lot of detail and it sounds like homework but try this
idea

Sub sumvarrows()
ac = 9
fr = 2 'or inputbox("Enter first row")
lr = Cells(Rows.Count, ac).End(xlUp).Row
MsgBox Application.Sum(Range(Cells(fr, ac), Cells(lr, ac)))
End Sub

 

[Don Guillett]

For archival purposes it is always nice to post your final solution for the
benefit of others.