Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
Hello,
I am using Excel Pro 2003 version. I have an iteration sheet with 2 variables which are Angle and Area. First I set the angle constant and have the iteration solved the area. Then I increase the angle and iteration solves the area for the new input angle. While I am doing the iteration, I want to have the angle and area results to be saved in result sheet. For instance for angle=40, solved area=500, for angle=41, solved area=550...and so on. What kind of formula I need to write in the "result" cell so I can have the angle and area values to be kept there while iteration goes on. I tried to write with IF statements but when the angle changes area is changing so does my "result" cell that suppose to keep the results. I tried to write like this: A1, where the angle is A2, where the area is =IF(A1=40;A2;"") =IF(A1=41;A2;"") I want to keep the results so "" part is wrong but I do not know what to write there so I can keep the results. Thank you very much. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
A cell may either have a formula in it that changes results when some part of
that formula changes, or it can have a value that does not change. It cannot have both. To save the calculated area results you will need a Visual Basic solution, and I have written one. Based on what you have written here, the only change you will have to make in the code is to change one line in it. In the code where it says: Const firstResultsCell = "E2" ' change as required change the E2 to the address of the cell where you were putting your =IF(A1=40;A2;"") formula. Example: if you had been putting that formula into cell G5, change the E2 to G5 so that it reads Const firstResultsCell = "G5" ' change as required What this will do is to put each new result into the first empty cell in the column identified by the E2 (or G5) and keep adding new results below that as you change the angle. You may select and delete those saved results any time you wish to start a new series of saved results. To put this code to use, follow these instructions: Open the workbook, and select the sheet where you perform the iterations and on which you wish to save the results. RIGHT-click on the worksheet's name tab, which will bring up a list of options. Choose [View Code] from that list of options. Copy the code below and paste it into the big empty code module that is presented to you when you choose [View Code]. Make the change to the value I discussed earlier (E2 to G5 or what ever you need it to be) Close the Visual Basic editor. It will work automatically for you at that point. Your Macro security will need to be set to Low or Medium for this to work. If you need help resetting macro security, let us know here and we will walk you through that process. Here is the code to place into your worksheet: Private Sub Worksheet_Change(ByVal Target As Range) 'address of the first cell to save results into 'all cells below it must not be used for anything 'other than saving results Const firstResultsCell = "E2" ' change as required 'cell address where you enter the angle Const angleEntryCell = "A1" 'address of the cell with the calculated area Const areaResultCell = "A2" Dim nextResultsRow As Long If Application.Intersect(Target, _ Range(angleEntryCell)) Is Nothing Then 'did not change the angle Exit Sub End If If Target = 0 Then 'cleared the entry, do not save new result Exit Sub End If 'save the results in the first empty 'cell below 'firstResultsCell' 'or in 'firstResultsCell if it is empty nextResultsRow = Cells(Rows.Count, _ Range(firstResultsCell).Column).End(xlUp).Row + 1 If nextResultsRow < Range(firstResultsCell).Row Or _ IsEmpty(Range(firstResultsCell)) Then nextResultsRow = Range(firstResultsCell).Row End If Range(firstResultsCell).Offset(nextResultsRow - _ Range(firstResultsCell).Row, 0) = _ Range(areaResultCell) End Sub "Tunja" wrote: Hello, I am using Excel Pro 2003 version. I have an iteration sheet with 2 variables which are Angle and Area. First I set the angle constant and have the iteration solved the area. Then I increase the angle and iteration solves the area for the new input angle. While I am doing the iteration, I want to have the angle and area results to be saved in result sheet. For instance for angle=40, solved area=500, for angle=41, solved area=550...and so on. What kind of formula I need to write in the "result" cell so I can have the angle and area values to be kept there while iteration goes on. I tried to write with IF statements but when the angle changes area is changing so does my "result" cell that suppose to keep the results. I tried to write like this: A1, where the angle is A2, where the area is =IF(A1=40;A2;"") =IF(A1=41;A2;"") I want to keep the results so "" part is wrong but I do not know what to write there so I can keep the results. Thank you very much. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
First of all, I would like to say a huge THANK YOU! :)
I appreciate that you have put time and effort on my quesiton. I am not good at Visial Basic but I will follow your instructions and let you know about the end situation. I have been trying to solve this problem. Thank you, thank you, thank you very much :) Regards "JLatham" wrote: |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
I followed your instruction. Thank you very much. The notes were perfect too.
- How may I save the results in another sheet? (Right now I can get the results in the same sheet) - May I put "IF statement" in Visial Basic part? Because my iteration formulation is not very good. My Area results suppose to obey 2 parameters. I could not put those 2 conditions in IF so, right now iteration finds a value by using the first parameter, if it doesn't cover second statement, then I manually change the formula in iteration and have it solved the area according the second parameter. Even if the area result doesn't cover the statements after using the second parameter then I say "for this angle, there is no logical solution to myself" and I increase the Angle. Since my "IF statement" in iteration is not very well prepared (my bad), sometimes I may have to change couple of things in the formula. Each time I change the formula, excel calculates the iteration of course. For every new result, visial basic writes a new result as we(you) asked from it. Since I can not make my IF statement in iteration better, maybe I could make the visial basic part better by adding IF. So I was wondering if I may use IF or WHEN statement in Visial Basic. OR I may study IF AND statement in iteration part and try to fit my 2 conditions in my iteration(I am not sure if it will work) :) It would be awesome if you may let me know how to have visial basic write the results in another sheet. Thank you very very much. I appreciate your help. Regards "JLatham" wrote: |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
Tunja,
Since you say it's writing the results to the same sheet, I would say that you managed to put the code into the proper place. The code below can be used to replace the other code, and you just need to make 2 changes to it this time so that the results will be written to the other sheet. Change the "E2" as before, and in the statement not far below it that reads: Const resultSheetName = "myResultsSheet" change the myResultsSheet to the name of the sheet that you wish the results to be placed onto. As for the IF statement, we could probably work that out to include in the code, but I would need to know the rules of when a result is valid to copy as it was calculated to be and when to write the "there is no logical solution" entry to the results sheet. Private Sub Worksheet_Change(ByVal Target As Range) 'address of the first cell to save results into 'all cells below it must not be used for anything 'other than saving results Const firstResultsCell = "E2" ' change as required 'change this to the name of the sheet you wish the 'results to be recorded on Const resultSheetName = "myResultsSheet" Dim resultsSheet As Worksheet 'cell address where you enter the angle Const angleEntryCell = "A1" 'address of the cell with the calculated area Const areaResultCell = "A2" Dim nextResultsRow As Long If Application.Intersect(Target, _ Range(angleEntryCell)) Is Nothing Then 'did not change the angle Exit Sub End If If Target = 0 Then 'cleared the entry, do not save new result Exit Sub End If 'save the results in the first empty 'cell below 'firstResultsCell' 'or in 'firstResultsCell if it is empty Set resultsSheet = ThisWorkbook.Worksheets(resultSheetName) nextResultsRow = resultsSheet.Cells(Rows.Count, _ Range(firstResultsCell).Column).End(xlUp).Row + 1 If nextResultsRow < Range(firstResultsCell).Row Or _ IsEmpty(resultsSheet.Range(firstResultsCell)) Then nextResultsRow = Range(firstResultsCell).Row End If resultsSheet.Range(firstResultsCell).Offset(nextRe sultsRow - _ Range(firstResultsCell).Row, 0) = _ Range(areaResultCell) End Sub "Tunja" wrote: I followed your instruction. Thank you very much. The notes were perfect too. - How may I save the results in another sheet? (Right now I can get the results in the same sheet) - May I put "IF statement" in Visial Basic part? Because my iteration formulation is not very good. My Area results suppose to obey 2 parameters. I could not put those 2 conditions in IF so, right now iteration finds a value by using the first parameter, if it doesn't cover second statement, then I manually change the formula in iteration and have it solved the area according the second parameter. Even if the area result doesn't cover the statements after using the second parameter then I say "for this angle, there is no logical solution to myself" and I increase the Angle. Since my "IF statement" in iteration is not very well prepared (my bad), sometimes I may have to change couple of things in the formula. Each time I change the formula, excel calculates the iteration of course. For every new result, visial basic writes a new result as we(you) asked from it. Since I can not make my IF statement in iteration better, maybe I could make the visial basic part better by adding IF. So I was wondering if I may use IF or WHEN statement in Visial Basic. OR I may study IF AND statement in iteration part and try to fit my 2 conditions in my iteration(I am not sure if it will work) :) It would be awesome if you may let me know how to have visial basic write the results in another sheet. Thank you very very much. I appreciate your help. Regards "JLatham" wrote: |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
Dear JLatham,
I put the new code. Visial Basic recorded the first calculated value. Then it did not write any more. Not in the same cell, not in the cell below. There is only one value written. This may be fixed but I think making the iteration formulation better may be easier than trying to fix the result part. If we can fix the iteration part, then I may use your first visial basic formulation which writes everything. I added a screenshot of my excel. By this way I may explain what I am doing better. http://img40.imageshack.us/img40/808/iterasyon.jpg I have 5 excel sheets. Index, Calculator (2), (1), (0) and Result. They are basically selfexplanatory. I am only going to make changes on Index. Calculator pages calculates (where iteration happens) and Result page will hold the results. YELLOW First I set calculator number from 2 to 0. This determines from which page, iteration will be taken from. Then I set the formula no from 6 to 1. This tells the PINK cell from which number of formula, it is going to show. GREEN Only takes the iteration results from one of the Calculator Sheets. What I am doing is first picking calculator page number, let's say 2. Then setting the angle equal to let's say 40. Then calculating areas for Formula No 1,2,3,4,5 and 6. So for 1 angle, I calculate 6 different areas (If there is one). Then I increase the angle, let's say 41 and and recalculate 6 more different area values. This goes like this. So in order to make it look clearer, I may try to tell this like a rope. calculator2-Formula1-angle40-area1 calculator2-Formula2-angle40-area2 calculator2-Formula3-angle40-area3 calculator2-Formula4-angle40-area4 calculator2-Formula5-angle40-area5 calculator2-Formula6-angle40-area6 so formula changes, area changes. We are done with angle 40. Next angle 41. When angle is 90, this means we are done with calculator2. Loop may start for calculator1 if it is asked for. This is the structure of my excel. During the iteration: calculated area has to be bigger than the minimum area and DELTA. I could not put 2 iterations in the same cell with IF statement. Right now iteration starts with DELTA0 condition. Iteration solves the formula and calculates the area. I check the area; if it is bigger than minimum area, it's fine otherwise I change the formula and put the formula related with AAsmin condition. I tried to have an iteration column with DELTA0 and another column checks this previous column and says =IF("this cell"Asmin;"this cell";"this cell+1"). This new column do not do the iteration. So I am changing the formula by hand. I know I gave so much information and it is kind of confusing. If I may put 2 iteration formulation in the same cell OR even if I may have the first iteration formula in one cell and 2nd one next to it and have the 2nd iteration check the 1st iteration, all my problems will be solved. If I may do this, then I can use your first visial basic code. I will appreciate if you may share your idea regarding this issue. Thank you very much. Regards Tunja |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
Dear JLatham,
I put the new code. Visial Basic wrote the first calculated value. Then it did not write any more. Not in the same cell, not in the cell below. There is only one value written. This may be fixed but I think making the iteration formulation better may be easier than trying to fix the result part. If we can fix the iteration part, then I may use your first visial basic formulation which writes everything. I added a screenshot of my excel. By this way I may explain what I am doing better. http://img40.imageshack.us/img40/808/iterasyon.jpg I have 5 excel sheets. Index, Calculator (2), (1), (0) and Result. They are basically selfexplanatory. I am only going to make changes on Index. Calculator pages calculates (where iteration happens) and Result page will hold the results. YELLOW First I set calculator number from 2 to 0. This determines from which page, iteration will be taken from. Then I set the formula no from 6 to 1. This tells the PINK cell from which number of formula, it is going to show. GREEN Only takes the iteration results from one of the Calculator Sheets. What I am doing is first picking calculator page number, let's say 2. Then setting the angle equal to let's say 40. Then calculating areas for Formula No 1,2,3,4,5 and 6. So for 1 angle, I calculate 6 different areas (If there is one). Then I increase the angle, let's say 41 and and recalculate 6 more different area values. This goes like this. So in order to make it look clearer, I may try to tell this like a rope. calculator2-Formula1-angle40-area1 calculator2-Formula2-angle40-area2 calculator2-Formula3-angle40-area3 calculator2-Formula4-angle40-area4 calculator2-Formula5-angle40-area5 calculator2-Formula6-angle40-area6 so formula changes, area changes. We are done with angle 40. Next angle 41. When angle is 90, this means we are done with calculator2. Loop may start for calculator1 if it is asked for. This is the structure of my excel. During the iteration: calculated area has to be bigger than the minimum area and DELTA. I could not put 2 iterations in the same cell with IF statement. Right now iteration starts with DELTA0 condition. Iteration solves the formula and calculates the area. I check the area; if it is bigger than minimum area, it's fine otherwise I change the formula and put the formula related with AAsmin condition. I tried to have an iteration column with DELTA0 and another column checks this previous column and says =IF("this cell"Asmin;"this cell";"this cell+1"). This new column do not do the iteration. So I am changing the formula by hand. I know I gave so much information and it is kind of confusing. If I may put 2 iteration formulation in the same cell OR even if I may have the first iteration formula in one cell and 2nd one next to it and have the 2nd iteration check the 1st iteration, all my problems will be solved. If I may do this, then I can use your first visial basic code. I will appreciate if you may share your idea regarding this issue. Thank you very much. Regards Tunja |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
I double checked the second code that I sent you and it does work for me
here. I assume that you want to change the entry in C4 and the value you wish to keep a record of is the value that appears in C11? Or are you wanting to keep all values that are calculated in D19 through D24? The new area calculated (C11) would only be recorded when you change the value in one cell (probably C4) but you could make that one cell C15 or C16 if either of those is the one you change most often. I think it would help us if you would send me a copy of the workbook so that I can see things more clearly and test code with the actual results, plus I could attempt to help with your iterations better. If you can, send an email to me with a copy of the Excel file attached to (remove spaces) HelpFrom @ JLatham Site. com and I will look at it all. "Tunja" wrote: Dear JLatham, I put the new code. Visial Basic wrote the first calculated value. Then it did not write any more. Not in the same cell, not in the cell below. There is only one value written. This may be fixed but I think making the iteration formulation better may be easier than trying to fix the result part. If we can fix the iteration part, then I may use your first visial basic formulation which writes everything. I added a screenshot of my excel. By this way I may explain what I am doing better. http://img40.imageshack.us/img40/808/iterasyon.jpg I have 5 excel sheets. Index, Calculator (2), (1), (0) and Result. They are basically selfexplanatory. I am only going to make changes on Index. Calculator pages calculates (where iteration happens) and Result page will hold the results. YELLOW First I set calculator number from 2 to 0. This determines from which page, iteration will be taken from. Then I set the formula no from 6 to 1. This tells the PINK cell from which number of formula, it is going to show. GREEN Only takes the iteration results from one of the Calculator Sheets. What I am doing is first picking calculator page number, let's say 2. Then setting the angle equal to let's say 40. Then calculating areas for Formula No 1,2,3,4,5 and 6. So for 1 angle, I calculate 6 different areas (If there is one). Then I increase the angle, let's say 41 and and recalculate 6 more different area values. This goes like this. So in order to make it look clearer, I may try to tell this like a rope. calculator2-Formula1-angle40-area1 calculator2-Formula2-angle40-area2 calculator2-Formula3-angle40-area3 calculator2-Formula4-angle40-area4 calculator2-Formula5-angle40-area5 calculator2-Formula6-angle40-area6 so formula changes, area changes. We are done with angle 40. Next angle 41. When angle is 90, this means we are done with calculator2. Loop may start for calculator1 if it is asked for. This is the structure of my excel. During the iteration: calculated area has to be bigger than the minimum area and DELTA. I could not put 2 iterations in the same cell with IF statement. Right now iteration starts with DELTA0 condition. Iteration solves the formula and calculates the area. I check the area; if it is bigger than minimum area, it's fine otherwise I change the formula and put the formula related with AAsmin condition. I tried to have an iteration column with DELTA0 and another column checks this previous column and says =IF("this cell"Asmin;"this cell";"this cell+1"). This new column do not do the iteration. So I am changing the formula by hand. I know I gave so much information and it is kind of confusing. If I may put 2 iteration formulation in the same cell OR even if I may have the first iteration formula in one cell and 2nd one next to it and have the 2nd iteration check the 1st iteration, all my problems will be solved. If I may do this, then I can use your first visial basic code. I will appreciate if you may share your idea regarding this issue. Thank you very much. Regards Tunja |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
Also, I believe that the formula you have in C11 could be re-written as:
=CHOOSE(C16,D19,D20,D21,D22,D23,D24) "Tunja" wrote: Dear JLatham, I put the new code. Visial Basic wrote the first calculated value. Then it did not write any more. Not in the same cell, not in the cell below. There is only one value written. This may be fixed but I think making the iteration formulation better may be easier than trying to fix the result part. If we can fix the iteration part, then I may use your first visial basic formulation which writes everything. I added a screenshot of my excel. By this way I may explain what I am doing better. http://img40.imageshack.us/img40/808/iterasyon.jpg I have 5 excel sheets. Index, Calculator (2), (1), (0) and Result. They are basically selfexplanatory. I am only going to make changes on Index. Calculator pages calculates (where iteration happens) and Result page will hold the results. YELLOW First I set calculator number from 2 to 0. This determines from which page, iteration will be taken from. Then I set the formula no from 6 to 1. This tells the PINK cell from which number of formula, it is going to show. GREEN Only takes the iteration results from one of the Calculator Sheets. What I am doing is first picking calculator page number, let's say 2. Then setting the angle equal to let's say 40. Then calculating areas for Formula No 1,2,3,4,5 and 6. So for 1 angle, I calculate 6 different areas (If there is one). Then I increase the angle, let's say 41 and and recalculate 6 more different area values. This goes like this. So in order to make it look clearer, I may try to tell this like a rope. calculator2-Formula1-angle40-area1 calculator2-Formula2-angle40-area2 calculator2-Formula3-angle40-area3 calculator2-Formula4-angle40-area4 calculator2-Formula5-angle40-area5 calculator2-Formula6-angle40-area6 so formula changes, area changes. We are done with angle 40. Next angle 41. When angle is 90, this means we are done with calculator2. Loop may start for calculator1 if it is asked for. This is the structure of my excel. During the iteration: calculated area has to be bigger than the minimum area and DELTA. I could not put 2 iterations in the same cell with IF statement. Right now iteration starts with DELTA0 condition. Iteration solves the formula and calculates the area. I check the area; if it is bigger than minimum area, it's fine otherwise I change the formula and put the formula related with AAsmin condition. I tried to have an iteration column with DELTA0 and another column checks this previous column and says =IF("this cell"Asmin;"this cell";"this cell+1"). This new column do not do the iteration. So I am changing the formula by hand. I know I gave so much information and it is kind of confusing. If I may put 2 iteration formulation in the same cell OR even if I may have the first iteration formula in one cell and 2nd one next to it and have the 2nd iteration check the 1st iteration, all my problems will be solved. If I may do this, then I can use your first visial basic code. I will appreciate if you may share your idea regarding this issue. Thank you very much. Regards Tunja |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Saving Excel Pro 2003 iteration results
I sent the email. Thank you very much again.
By the way, I deleted Calculator (1) and (0). Only one calculator is capable of doing the calculation. Thanks again. "JLatham" wrote: Also, I believe that the formula you have in C11 could be re-written as: =CHOOSE(C16,D19,D20,D21,D22,D23,D24) "Tunja" wrote: Dear JLatham, I put the new code. Visial Basic wrote the first calculated value. Then it did not write any more. Not in the same cell, not in the cell below. There is only one value written. This may be fixed but I think making the iteration formulation better may be easier than trying to fix the result part. If we can fix the iteration part, then I may use your first visial basic formulation which writes everything. I added a screenshot of my excel. By this way I may explain what I am doing better. http://img40.imageshack.us/img40/808/iterasyon.jpg I have 5 excel sheets. Index, Calculator (2), (1), (0) and Result. They are basically selfexplanatory. I am only going to make changes on Index. Calculator pages calculates (where iteration happens) and Result page will hold the results. YELLOW First I set calculator number from 2 to 0. This determines from which page, iteration will be taken from. Then I set the formula no from 6 to 1. This tells the PINK cell from which number of formula, it is going to show. GREEN Only takes the iteration results from one of the Calculator Sheets. What I am doing is first picking calculator page number, let's say 2. Then setting the angle equal to let's say 40. Then calculating areas for Formula No 1,2,3,4,5 and 6. So for 1 angle, I calculate 6 different areas (If there is one). Then I increase the angle, let's say 41 and and recalculate 6 more different area values. This goes like this. So in order to make it look clearer, I may try to tell this like a rope. calculator2-Formula1-angle40-area1 calculator2-Formula2-angle40-area2 calculator2-Formula3-angle40-area3 calculator2-Formula4-angle40-area4 calculator2-Formula5-angle40-area5 calculator2-Formula6-angle40-area6 so formula changes, area changes. We are done with angle 40. Next angle 41. When angle is 90, this means we are done with calculator2. Loop may start for calculator1 if it is asked for. This is the structure of my excel. During the iteration: calculated area has to be bigger than the minimum area and DELTA. I could not put 2 iterations in the same cell with IF statement. Right now iteration starts with DELTA0 condition. Iteration solves the formula and calculates the area. I check the area; if it is bigger than minimum area, it's fine otherwise I change the formula and put the formula related with AAsmin condition. I tried to have an iteration column with DELTA0 and another column checks this previous column and says =IF("this cell"Asmin;"this cell";"this cell+1"). This new column do not do the iteration. So I am changing the formula by hand. I know I gave so much information and it is kind of confusing. If I may put 2 iteration formulation in the same cell OR even if I may have the first iteration formula in one cell and 2nd one next to it and have the 2nd iteration check the 1st iteration, all my problems will be solved. If I may do this, then I can use your first visial basic code. I will appreciate if you may share your idea regarding this issue. Thank you very much. Regards Tunja |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving files in Excel 2003 | Excel Discussion (Misc queries) | |||
Saving .csv files in Excel 2003 | Excel Discussion (Misc queries) | |||
Iteration and saving. | Excel Discussion (Misc queries) | |||
Iteration and saving. | Excel Discussion (Misc queries) | |||
Saving a workbook in Excel 2003 | Excel Discussion (Misc queries) |