Help with SUM code
hi,
your problem is "Cells(Target.Row, 24).Value = SUM
(Target.Row(2):Target.Row(23))
to enable the sum function to work for you, you need to
set the cell formula just as you would when entering the
sum function manually.
try:
Cells(Target.Row, 24).formula= "=SUM(B" & Target.Row
& ":X" & Target.Row &")"
Steve
-----Original Message-----
Hi Guys
Trying to write a macro to add up a bunch of cells on the
same row in a sheet to a "total" cell everytime one cell
in the range specified changes. There are a number of rows
that can be modified. Here is the code I am using..I am
getting error messages with the SUM formula..what is the
proper syntax for this??, also how would I add a piece of
code to check whether the cell being modified is a number
and not text as some of the rows contain month names, day
names and also dates in dd/mm/yy format down through the
file??. Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column 1 & Target.Column < 24 Then
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 24).Value = SUM (Target.Row
(2):Target.Row(23))
Else
Cells(Target.Row, Target.Column).ClearContents
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
.
|