Visual Basic Code

M

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.
 
B

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)
 
A

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

______________________
 
D

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
 
B

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)
 
M

Mike

Thanks for your help, all sorted now.

Don Guillett said:
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
Click to expand...
 
D

Don Guillett

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