Saving Excel Pro 2003 iteration results

[Tunja]

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.

 

[JLatham]

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]

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


:

 

[Tunja]

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]

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(nextResultsRow - _
Range(firstResultsCell).Row, 0) = _
Range(areaResultCell)
End Sub

 

[Tunja]

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 DELTA>0 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 A>Asmin condition. I tried to have an iteration column with
DELTA>0 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

 

[Tunja]

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 DELTA>0 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 A>Asmin condition. I tried to have an iteration column with
DELTA>0 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

 

[JLatham]

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.

 

[JLatham]

Also, I believe that the formula you have in C11 could be re-written as:
=CHOOSE(C16,D19,D20,D21,D22,D23,D24)

 

[Tunja]

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.