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)

"Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
> 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.
>
>

[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

______________________

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:OytMiTskGHA.836@TK2MSFTNGP02.phx.gbl...
>
> 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)
>
> "Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
> news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
>> 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]

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)

"Art®" <pbaprof@DROPCAPSadelphia.net> wrote in message
news:#$x53xskGHA.1264@TK2MSFTNGP05.phx.gbl...
> 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
>
> ______________________
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:OytMiTskGHA.836@TK2MSFTNGP02.phx.gbl...
> >
> > 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)
> >
> > "Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
> > news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
> >> 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.
> >>
> >>
> >
> >
>
>

[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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
> 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.
>

[Mike]

Thanks for your help, all sorted now.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:upLa69skGHA.5036@TK2MSFTNGP04.phx.gbl...
> 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
> SalesAid Software
> dguillett1@austin.rr.com
> "Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
> news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
>> 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.
>>
>
>

[Don Guillett]

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

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
news:ORXqhSwkGHA.2304@TK2MSFTNGP02.phx.gbl...
> Thanks for your help, all sorted now.
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:upLa69skGHA.5036@TK2MSFTNGP04.phx.gbl...
>> 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
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Mike" <mike@mike1313.wanadoo.co.uk> wrote in message
>> news:uv%23svHskGHA.4200@TK2MSFTNGP05.phx.gbl...
>>> 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.
>>>
>>
>>
>
>