| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-calculate after a cell is filled in - macro shouldn't be loop
Hi
I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-calculate after a cell is filled in - macro shouldn't be loop
On 6 ruj, 12:04, raphiel2063
wrote: Hi I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub Hello Try with this in the 1. row of source "Application.Calculation = xlManual" and on the end of source put this "Application.Calculation = xlAutomatic" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-calculate after a cell is filled in - macro shouldn't be loop
Huh? What have I got wrong in the code? Anybody.... can you give me
instructions of what to do as I'm relatively new to vba. "raphiel2063" wrote: Hi I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-calculate after a cell is filled in - macro shouldn't be loop
Your code is changing the worksheet, which then fires the code again! To
avoid this, put this line before your If Not Intersect line: Application.EnableEvents=False and add this line before your End Sub line: Application.EnableEvents=True HTH, James "raphiel2063" wrote in message ... Huh? What have I got wrong in the code? Anybody.... can you give me instructions of what to do as I'm relatively new to vba. "raphiel2063" wrote: Hi I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-calculate after a cell is filled in - macro shouldn't be
Worked a charm, cheers.
"Zone" wrote: Your code is changing the worksheet, which then fires the code again! To avoid this, put this line before your If Not Intersect line: Application.EnableEvents=False and add this line before your End Sub line: Application.EnableEvents=True HTH, James "raphiel2063" wrote in message ... Huh? What have I got wrong in the code? Anybody.... can you give me instructions of what to do as I'm relatively new to vba. "raphiel2063" wrote: Hi I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-calculate after a cell is filled in - macro shouldn't be
Great! Glad to help.
"raphiel2063" wrote in message ... Worked a charm, cheers. "Zone" wrote: Your code is changing the worksheet, which then fires the code again! To avoid this, put this line before your If Not Intersect line: Application.EnableEvents=False and add this line before your End Sub line: Application.EnableEvents=True HTH, James "raphiel2063" wrote in message ... Huh? What have I got wrong in the code? Anybody.... can you give me instructions of what to do as I'm relatively new to vba. "raphiel2063" wrote: Hi I'm still struggling with the below as the macro appears to be contantly looping. I've set it up so if any of the input cells are used it will trigger the calculations to be performed on the corresponding cell in the same row. However, excel just freezes and I have to abort the macro.... any ideas? Private Sub Worksheet_Change(ByVal Target As Range) 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 End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Insert the present time when a cell is filled with a data, | New Users to Excel | |||
| Calculate value in a text filled cell | Excel Worksheet Functions | |||
| MACRO: Catching only filled cell data | Excel Discussion (Misc queries) | |||
| Run Macro when cell is filled | Excel Discussion (Misc queries) | |||
| How do I auto fill long col. filling blanks with last filled cell | Excel Discussion (Misc queries) | |||
Linear Mode
