Autofill is overwriting the column title.....

's Computer Specifications

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

's Computer Specifications

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.
raphiel2063 wrote:
> 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

's Computer Specifications

Oops. I said screen updating. I meant enable events.
JW wrote:
> 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.
> raphiel2063 wrote:
> > 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

's Computer Specifications

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

"raphiel2063" wrote:

> 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

's Computer Specifications

Worked great. Thanks for that.

"p45cal" wrote:

> 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
>
>
> "raphiel2063" wrote:
>
> > 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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
RE: Automating to autofill column B based on column A entry	Shane Devenshire	Microsoft Excel Misc	0	2nd Dec 2008 10:02 PM
repeating data in column to empty cells below without overwriting	=?Utf-8?B?amVmZmNoaW5h?=	Microsoft Excel Worksheet Functions	1	26th Apr 2007 05:50 PM
Named range=Column title,comumn title in cellB6 use B6in equation	=?Utf-8?B?R3JhaGFt?=	Microsoft Excel Misc	2	21st Jul 2006 10:03 AM
Create a column upon insertion or overwriting of data.	Swiatkowski Peter	Microsoft Excel Discussion	1	20th Dec 2005 11:43 AM
Column E cell contents added into Column D contents (not overwriting data but mixing)	creativetechguy	Microsoft Excel Misc	2	5th Aug 2004 07:32 PM