Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
I have a range of data that encompasses anywhere from 20 rows to 50 rows and
5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
You need to either disable events while doing your Calculate_Results
stuff application.enableevents=false Calculate_Results application.enableevents=true or use a flag inside your sheetchange handler Static bProcessing as boolean if bprocessing then exit sub bProcessing=true Calculate_Results bProcessing=false Hope this helps Tim. "Gary Paris" wrote in message ... I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
You can tell excel to stop looking for changes.
Then your code can make the change it needs to make and the event won't be called. In general: application.enableevents = false 'tell excel to stop looking 'your code that does something that would have fired an event application.enableevents = true 'tell excel to start looking again. Gary Paris wrote: I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
Thanks to the guys who replied, but I have another question. Is it possible
to call the Calculate_Results routine only when data changes in the named Range? "Gary Paris" wrote in message ... I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
You could do something like this that just gets out early if the change isn't in
the right range: Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Sh.Range("NamedRangeHere")) Is Nothing Then Exit Sub End If End Sub (This means that each sheet has to have a range named: NamedRangeHere) Gary Paris wrote: Thanks to the guys who replied, but I have another question. Is it possible to call the Calculate_Results routine only when data changes in the named Range? "Gary Paris" wrote in message ... I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
I put this code in the proper place and it works. Another problem is that I
have three worksheets in the book and I have a Sheets("Expenses").Select statement in the routine. Each time the routine runs, I am directed to the Expenses sheet. Is there anyway around this? Can I possibly save the current sheet name, the current cell I am in and then go back when the routine completes? Thanks, Gary "Dave Peterson" wrote in message ... You can tell excel to stop looking for changes. Then your code can make the change it needs to make and the event won't be called. In general: application.enableevents = false 'tell excel to stop looking 'your code that does something that would have fired an event application.enableevents = true 'tell excel to start looking again. Gary Paris wrote: I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
Most times, you don't need to select a worksheet or range to work with it. You
can modify objects directly. worksheets("expenses").select range("a1").select activecell.value = "Hi" could be replaced with: worksheets("expenses").range("a1").value = "Hi" It usually makes the code much easier to read/decipher and it makes the code execute faster. === But you could do: Dim CurSelection as range Dim curActivecell as range set curSelection = Selection set curActivecell = activecell 'do your stuff application.goto curSelection curactivecell.activate Gary Paris wrote: I put this code in the proper place and it works. Another problem is that I have three worksheets in the book and I have a Sheets("Expenses").Select statement in the routine. Each time the routine runs, I am directed to the Expenses sheet. Is there anyway around this? Can I possibly save the current sheet name, the current cell I am in and then go back when the routine completes? Thanks, Gary "Dave Peterson" wrote in message ... You can tell excel to stop looking for changes. Then your code can make the change it needs to make and the event won't be called. In general: application.enableevents = false 'tell excel to stop looking 'your code that does something that would have fired an event application.enableevents = true 'tell excel to start looking again. Gary Paris wrote: I have a range of data that encompasses anywhere from 20 rows to 50 rows and 5 columns. I have a routine that I call Calculate_Results that is called from the Workbook_SheetChange routine. The problem is that once the results are calculated, I would like to place totals into a cell on another worksheet. Problem is the routine goes forever. Calculate_Results generates a SheetChange and SheetChange calls Calculate_Results. How can I call Calculate_Results whenever there is data modification in the area I would like to change? Thanks, Gary -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
On Fri, 24 Dec 2004 17:08:00 -0800, "Gary Paris" wrote:
Thanks for your input. Code works OK, but another question. Is there a way that I can call my Calc routine only when leaving the Expenses sheet? Put this in your ThisWorkBook module: Sub Workbook_SheetDeactivate(ByVal Sh As Object) If Sh.Name = "Expenses" Then ' code goes here End If End Sub Don <donwiss at panix.com. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
On Fri, 24 Dec 2004 15:48:46 -0800, "Tim Williams"
<saxifrax@pacbell*dot*net wrote: or use a flag inside your sheetchange handler Static bProcessing as boolean if bprocessing then exit sub bProcessing=true Calculate_Results bProcessing=false I started with the flag way, and I find it flexible to use for all sorts of: don't do this if a macro is running. It is my Macro Running flag. I define it globally as: Public MRflag as Boolean I've never used Static. Don <donwiss at panix.com. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating results when data changes
On Fri, 24 Dec 2004 17:08:00 -0800, Gary Paris wrote:
Sheets("Expenses").Select For Each myCell In Range("Paid_By").Cells Range(myCell.Address).Select Select Case myCell Case "Gary" Gary_Total = Gary_Total + ActiveCell.Offset(0, -2).Value Case "Dom" Dom_Total = Dom_Total + ActiveCell.Offset(0, -2).Value End Select Next myCell This is how I would do this. No sheet selection needed. Gary_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Gary",Ra nge("Paid_By").Offset(0,-2)) Dom_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Dom",Ran ge("Paid_By").Offset(0,-2)) Don <donwiss at panix.com. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incredible code ! Thanks
Wow,
You did in two lines what it took me many lines and quite a few hours to figure out. Thanks for the great code snippit. Plus, the sheets don't get selected and the active cell isn't changed. I still have lots to learn, but I guess that is what the newsgroups are all about! Gary "Don Wiss" wrote in message ... On Fri, 24 Dec 2004 17:08:00 -0800, Gary Paris wrote: Sheets("Expenses").Select For Each myCell In Range("Paid_By").Cells Range(myCell.Address).Select Select Case myCell Case "Gary" Gary_Total = Gary_Total + ActiveCell.Offset(0, -2).Value Case "Dom" Dom_Total = Dom_Total + ActiveCell.Offset(0, -2).Value End Select Next myCell This is how I would do this. No sheet selection needed. Gary_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Gary",Ra nge("Paid_By").Offset(0,-2)) Dom_Total = WorksheetFunction.SumIf(Range("Paid_By"),"Dom",Ran ge("Paid_By").Offset(0,-2)) Don <donwiss at panix.com. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating results from cells containing formulas | Excel Worksheet Functions | |||
calculating filtered results | Excel Discussion (Misc queries) | |||
PRODUCT RESULTS/VALUE ARE NOT CALCULATING CORRECTLY | Excel Worksheet Functions | |||
Filter data & sum or avg the results? | Excel Worksheet Functions | |||
calculating results in formulas | Excel Discussion (Misc queries) |