PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Auto-calculate after a cell is filled in - macro shouldn't be loop

Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?cmFwaGllbDIwNjM=?=
Guest
Posts: n/a
 
      6th Sep 2007
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 With Quote
 
 
 
 
I.R;))
Guest
Posts: n/a
 
      6th Sep 2007
On 6 ruj, 12:04, raphiel2063 <raphiel2...@discussions.microsoft.com>
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"
 
Reply With Quote
 
=?Utf-8?B?cmFwaGllbDIwNjM=?=
Guest
Posts: n/a
 
      6th Sep 2007
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 With Quote
 
Zone
Guest
Posts: n/a
 
      6th Sep 2007
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" <(E-Mail Removed)> wrote in message
news:59DFD76A-444C-4C5F-B84D-(E-Mail Removed)...
> 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 With Quote
 
=?Utf-8?B?cmFwaGllbDIwNjM=?=
Guest
Posts: n/a
 
      6th Sep 2007
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" <(E-Mail Removed)> wrote in message
> news:59DFD76A-444C-4C5F-B84D-(E-Mail Removed)...
> > 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 With Quote
 
Zone
Guest
Posts: n/a
 
      6th Sep 2007
Great! Glad to help.
"raphiel2063" <(E-Mail Removed)> wrote in message
news:A3937715-B4A5-44A8-AD98-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:59DFD76A-444C-4C5F-B84D-(E-Mail Removed)...
>> > 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 With Quote
 
 
 
Reply

« how to write to macro to only delete rows under certain conditions | Application.VLookup Problem... »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Insert the present time when a cell is filled with a data, Dinanath Microsoft Excel New Users 2 8th Mar 2009 08:28 PM
Auto-calculate after a cell is filled in - Macro is looping!!! =?Utf-8?B?cmFwaGllbDIwNjM=?= Microsoft Excel Programming 2 6th Sep 2007 04:08 PM
Calculate value in a text filled cell =?Utf-8?B?RC0t?= Microsoft Excel Worksheet Functions 6 12th Jul 2007 02:05 AM
Run Macro when cell is filled =?Utf-8?B?SmFja1I=?= Microsoft Excel Misc 4 21st Mar 2006 11:26 PM
How do I auto fill long col. filling blanks with last filled cell =?Utf-8?B?TUJCZWdpbm5lcg==?= Microsoft Excel Misc 3 15th Jan 2006 12:44 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 05:00 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top