Auto-calculate after a cell is filled in - macro shouldn't be loop

R

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
 
I

I.R;))

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
Click to expand...

Hello

Try with this in the 1. row of source "Application.Calculation =
xlManual" and on the end of source put this "Application.Calculation =
xlAutomatic"
 
R

raphiel2063

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

Zone

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

raphiel2063

Worked a charm, cheers.

Zone said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Z

Zone

Great! Glad to help.
raphiel2063 said:
Worked a charm, cheers.

Zone said:
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 said:
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto-calculate after a cell is filled in - Macro is looping!!! 2
Autofill is overwriting the column title..... 4
Macro autofills formula when a cell value is changed 4
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Insert row gives run-time error 13 message 1
Formula in macro gives "Run-time error '1004':" 1
Modify range in VBA 9

Top