Visual Basic Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 18 Jun 2006 11:28 GMT

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.

Reply to this Message

Bob Phillips - 18 Jun 2006 11:49 GMT

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

Signature

HTH

Bob Phillips

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

> Hi All,
>
[quoted text clipped - 5 lines]
>
> Thanks Mike.

Reply to this Message

Art® - 18 Jun 2006 12:43 GMT

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

______________________

> iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
> Cells(iLastRow+1,"A").Formula = "=SUM(A" & _
[quoted text clipped - 10 lines]
>>
>> Thanks Mike.

Reply to this Message

Bob Phillips - 18 Jun 2006 13:17 GMT

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)

> 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
[quoted text clipped - 33 lines]
> >>
> >> Thanks Mike.

Reply to this Message

Don Guillett - 18 Jun 2006 13:05 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi All,
>
[quoted text clipped - 5 lines]
>
> Thanks Mike.

Reply to this Message

Mike - 18 Jun 2006 19:25 GMT

Thanks for your help, all sorted now.

> You don't give a lot of detail and it sounds like homework but try this
> idea
[quoted text clipped - 15 lines]
>>
>> Thanks Mike.

Reply to this Message

Don Guillett - 18 Jun 2006 22:07 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Thanks for your help, all sorted now.
>
[quoted text clipped - 17 lines]
>>>
>>> Thanks Mike.

Reply to this Message