workbook_change

K

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 i
that column (from 19 to the last filled cell -there are also empt
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 (exep
filling the active cell)

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

can anyone help me please

Thanks in advanc
 
D

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
 
B

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

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 said:
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)
Click to expand...
 
Top