| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill is overwriting the column title.....
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill is overwriting the column title.....
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill is overwriting the column title.....
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill is overwriting the column title.....
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill is overwriting the column title.....
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Column after specific text title the new column and add for | Excel Discussion (Misc queries) | |||
| Automating to autofill column B based on column A entry | Excel Discussion (Misc queries) | |||
| repeating data in column to empty cells below without overwriting | Excel Worksheet Functions | |||
| Named range=Column title,comumn title in cellB6 use B6in equation | Excel Discussion (Misc queries) | |||
| Sumif based on column A and title of another column | Excel Discussion (Misc queries) | |||
Linear Mode
