Autofill is overwriting the column title.....

[raphiel2063]

Hi

I've got the below macro which autfills details into the corresponding rows
in my worksheet when certain cells are modified. Unfortunately, it also keeps
overwriting the column title and inserting the formulas in for some reason.
Is there a way to modify it to ignore the first row?

Below is what I've got....


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then

Dim r As Long
r = Target.Row

If Cells(r, "B").Value <> "" Or _
Cells(r, "C").Value <> "" Or _
Cells(r, "E").Value <> "" Then

' The below equation is the original sumif formula I was using in the cell
' =SUMIF($U$4:$BL$4,$A$2,U5:BL5)

' the below perform sumif's on the same range
Cells(r, "I").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R2C1,RC[12]:RC[55])"
Cells(r, "J").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R3C1,RC[11]:RC[54])"
Cells(r, "K").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R4C1,RC[10]:RC[53])"

' this totals the sumif's
Cells(r, "L").FormulaR1C1 =
"=SUM(RC[-3]:RC[-1])+SUM(RC[5]:RC[7])"

' These take the result of the above sumif and multiply it by a unit price
Cells(r, "M").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-5]"
Cells(r, "N").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-6]"
Cells(r, "O").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-7]"

' This gives a grand total of the above three sub-totals
Cells(r, "P").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
End If
End If

Application.EnableEvents = True

End Sub

 

[JW]

After you set your r variable equal to Target.Row, just throw an If
statement in there like If r=1 Then Whatever. Since you are setting
the r variable after you turn off screen updating, you will need to be
sure to turn it back on befire exiting if r=1. You could use a GoTo
statement to toss is down to the end.

 

[JW]

Oops. I said screen updating. I meant enable events.

After you set your r variable equal to Target.Row, just throw an If
statement in there like If r=1 Then Whatever. Since you are setting
the r variable after you turn off screen updating, you will need to be
sure to turn it back on befire exiting if r=1. You could use a GoTo
statement to toss is down to the end.

Hi

I've got the below macro which autfills details into the corresponding rows
in my worksheet when certain cells are modified. Unfortunately, it also keeps
overwriting the column title and inserting the formulas in for some reason.
Is there a way to modify it to ignore the first row?

Below is what I've got....


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then

Dim r As Long
r = Target.Row

If Cells(r, "B").Value <> "" Or _
Cells(r, "C").Value <> "" Or _
Cells(r, "E").Value <> "" Then

' The below equation is the original sumif formula I was using in the cell
' =SUMIF($U$4:$BL$4,$A$2,U5:BL5)

' the below perform sumif's on the same range
Cells(r, "I").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R2C1,RC[12]:RC[55])"
Cells(r, "J").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R3C1,RC[11]:RC[54])"
Cells(r, "K").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R4C1,RC[10]:RC[53])"

' this totals the sumif's
Cells(r, "L").FormulaR1C1 =
"=SUM(RC[-3]:RC[-1])+SUM(RC[5]:RC[7])"

' These take the result of the above sumif and multiply it by a unit price
Cells(r, "M").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-5]"
Cells(r, "N").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-6]"
Cells(r, "O").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-7]"

' This gives a grand total of the above three sub-totals
Cells(r, "P").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
End If
End If

Application.EnableEvents = True

End Sub

 

[p45cal]

replacing
If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then
with
If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing And Target.Row <> 1 Then

may be one answer but target.row will still be 1 when a range is edited
which may be several rows but include row 1. So the cells not on row 1 will
not be processed. However, since your code doesn't allow for this anyway,
it's unlikely to be a problem.
p45cal

 

[raphiel2063]

Worked great. Thanks for that.

replacing
If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then
with
If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing And Target.Row <> 1 Then

may be one answer but target.row will still be 1 when a range is edited
which may be several rows but include row 1. So the cells not on row 1 will
not be processed. However, since your code doesn't allow for this anyway,
it's unlikely to be a problem.
p45cal
--
p45cal

Hi

I've got the below macro which autfills details into the corresponding rows
in my worksheet when certain cells are modified. Unfortunately, it also keeps
overwriting the column title and inserting the formulas in for some reason.
Is there a way to modify it to ignore the first row?

Below is what I've got....


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Range(Target.Address), Range("A:EE")) _
Is Nothing Then

Dim r As Long
r = Target.Row

If Cells(r, "B").Value <> "" Or _
Cells(r, "C").Value <> "" Or _
Cells(r, "E").Value <> "" Then

' The below equation is the original sumif formula I was using in the cell
' =SUMIF($U$4:$BL$4,$A$2,U5:BL5)

' the below perform sumif's on the same range
Cells(r, "I").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R2C1,RC[12]:RC[55])"
Cells(r, "J").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R3C1,RC[11]:RC[54])"
Cells(r, "K").FormulaR1C1 =
"=SUMIF(R4C21:R4C64,R4C1,RC[10]:RC[53])"

' this totals the sumif's
Cells(r, "L").FormulaR1C1 =
"=SUM(RC[-3]:RC[-1])+SUM(RC[5]:RC[7])"

' These take the result of the above sumif and multiply it by a unit price
Cells(r, "M").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-5]"
Cells(r, "N").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-6]"
Cells(r, "O").FormulaR1C1 = "=(RC[-4]+RC[4])*RC[-7]"

' This gives a grand total of the above three sub-totals
Cells(r, "P").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
End If
End If

Application.EnableEvents = True

End Sub