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
|
|
|