Worksheet_Change for Row Insert
 

Hi,

I have a named range which includes about 6columns and 6 rows. When a user
inserts a row within the named range, I need a SUMIF formula to be added in
the last column of the inserted row. I've pasted my current Worksheet_Change
Sub routine below, but I'm not too sure how to amend it so the code that I
currently have still works and the macro will also add the SUMIF formula when
the row is inserted.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim FilteredClients As Range
If Not Intersect(Target, Target.Worksheet.Range("ClientCells")) Is
Nothing Then
ThisWorkbook.PopulateValidationLists
End If
End Sub


Help is greatly appreciated. Thanks.
Simon

Worksheet_Change for Row Insert
 

Hi Simon, I believe this will do what you want. You will have to modify
this line:

Range("I" & rw).Formula = "=SumIf(A" & rw & ":H" & rw & ", ""=0"",
_
A" & rw + 1 & ":B" & rw + 1 & ")"

to get the correct column and SumIf formula. You can insert is just before
the End If line in your existing macro.


Dim rw As Long
rw = Target.Row
If WorksheetFunction.CountA(Range(rw & ":" & rw)) = 0 Then
Range("I" & rw).Formula = "=SumIf(A" & rw & ":H" & rw & ", ""=0"",
_
A" & rw + 1 & ":B" & rw + 1 & ")"
End If


"Simon" wrote in message
...
Hi,

I have a named range which includes about 6columns and 6 rows. When a
user
inserts a row within the named range, I need a SUMIF formula to be added
in
the last column of the inserted row. I've pasted my current
Worksheet_Change
Sub routine below, but I'm not too sure how to amend it so the code that I
currently have still works and the macro will also add the SUMIF formula
when
the row is inserted.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim FilteredClients As Range
If Not Intersect(Target, Target.Worksheet.Range("ClientCells")) Is
Nothing Then
ThisWorkbook.PopulateValidationLists
End If
End Sub


Help is greatly appreciated. Thanks.
Simon