Auto-calculate after a cell is filled in - Macro is looping!!!

[raphiel2063]

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

 

[Jim Rech]

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

 

[raphiel2063]

Worked a charm, cheers.