Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Programming | |||
Worksheet_Change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |