workbook_change

[kizzie]

Hello,

I need a macro that does the following:

If a value entered in the range below row 19, the sum of the values in that column (from 19 to the last filled cell -there are also empty cells) must be added to the cell on row 14 of that column

If a value is entered above row 19, nothing has to happen (exept filling the active cell)

I can't use any formulas, because the cells that keep the sums, are also manipulated by other scripts.

can anyone help me please

Thanks in advance

[Don Guillett]

try this in the sheet module desired. Modify to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Row < 8 Then Exit Sub
'one line below
Range("a6") = Application.Sum(Range("a8:a" & Cells(Rows.Count,
"a").End(xlUp).Row))
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"kizzie" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I need a macro that does the following:
>
> If a value entered in the range below row 19, the sum of the values in
> that column (from 19 to the last filled cell -there are also empty
> cells) must be added to the cell on row 14 of that column
>
> If a value is entered above row 19, nothing has to happen (exept
> filling the active cell)
>
> I can't use any formulas, because the cells that keep the sums, are
> also manipulated by other scripts.
>
> can anyone help me please
>
> Thanks in advance
>
>
> --
> kizzie
> ------------------------------------------------------------------------
> kizzie's Profile:
http://www.excelforum.com/member.php...o&userid=26092
> View this thread: http://www.excelforum.com/showthread...hreadid=394919
>

[Bob Phillips]

Hi Kizzie,

this should do it

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iLastRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Row > 14 Then
iLastRow = Me.Cells(Rows.Count, .Column).End(xlUp).Row
Me.Cells(14, .Column).Value = Application.Sum( _
Me.Range(Me.Cells(15, .Column), Me.Cells(iLastRow,
..Column)))
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"kizzie" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I need a macro that does the following:
>
> If a value entered in the range below row 19, the sum of the values in
> that column (from 19 to the last filled cell -there are also empty
> cells) must be added to the cell on row 14 of that column
>
> If a value is entered above row 19, nothing has to happen (exept
> filling the active cell)
>
> I can't use any formulas, because the cells that keep the sums, are
> also manipulated by other scripts.
>
> can anyone help me please
>
> Thanks in advance
>
>
> --
> kizzie
> ------------------------------------------------------------------------
> kizzie's Profile:
http://www.excelforum.com/member.php...o&userid=26092
> View this thread: http://www.excelforum.com/showthread...hreadid=394919
>

[Bob Phillips]

Kizzie,

A typo in my code

Me.Range(Me.Cells(15, .Column), Me.Cells(iLastRow,
..Column)))

should be

Me.Range(Me.Cells(19, .Column), _
Me.Cells(iLastRow, .Column)))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:%23$%[email protected]...
> Hi Kizzie,
>
> this should do it
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim iLastRow As Long
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> If .Row > 14 Then
> iLastRow = Me.Cells(Rows.Count, .Column).End(xlUp).Row
> Me.Cells(14, .Column).Value = Application.Sum( _
> Me.Range(Me.Cells(15, .Column), Me.Cells(iLastRow,
> .Column)))
> End If
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "kizzie" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Hello,
> >
> > I need a macro that does the following:
> >
> > If a value entered in the range below row 19, the sum of the values in
> > that column (from 19 to the last filled cell -there are also empty
> > cells) must be added to the cell on row 14 of that column
> >
> > If a value is entered above row 19, nothing has to happen (exept
> > filling the active cell)
> >
> > I can't use any formulas, because the cells that keep the sums, are
> > also manipulated by other scripts.
> >
> > can anyone help me please
> >
> > Thanks in advance
> >
> >
> > --
> > kizzie
> > ------------------------------------------------------------------------
> > kizzie's Profile:
> http://www.excelforum.com/member.php...o&userid=26092
> > View this thread:
http://www.excelforum.com/showthread...hreadid=394919
> >
>
>