| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-calculate after a cell is filled in - Macro is looping!!!
Worked a charm, cheers.
"Jim Rech" wrote: Worksheet_Change is called when you make an entry in a sheet, either manually or by macro. Since your macro is changing the sheet it is in effect calling itself (again and again). Whenever you change a sheet change in sub Worksheet_Change put Application.EnableEvents=False at the beginning of the sub and then set it to True at the end. -- Jim "raphiel2063" wrote in message ... | 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 | | |
| 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 | |||
| Auto-calculate after a cell is filled in - macro shouldn't be loop | Excel Programming | |||
| Calculate value in a text filled cell | Excel Worksheet Functions | |||
| 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) | |||
Threaded Mode
