workbook_change
 

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

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

"kizzie" wrote in
message ...

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

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" wrote in
message ...

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

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" wrote in message
...
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" wrote in
message ...

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