Solver Macro not returning values

[BunInJapan]

Dear All,

Hello! First time poster here on the Forum! I just hope that someone can help me before I tear all my hair out over this!

So, I have a lot of data to process, arranged in columns such that Columns B and C are the x and y axis of a plot, then Cols D and E are the next plot, etc. Example data file is here:
http://www.excelforum.com/attachment...1&d=1317898045

I recorded a macro (in the above file) by choosing the first two columns, copying and pasting the data into this file (so Solver could work on the data): http://www.excelforum.com/attachment...1&d=1317898316

Then I ran Solver and got values in cells D3 and G3. Then I copy the value of D3 and put it back into the first file, on the second worksheet "GPa results", into cell C3.

This process then repeats, but the value of D3 from Solver goes into "GPa results" C4, C5 C6 and so on.

Problem is, when I run the recorded macro, it does not seem to run Solver correctly, nor does it copy the values from cell D3 into the "GPa results" worksheet. Usually it finds just one value and then copies it repeatedly!

Here is the code for the first three plots of the recorded macro (included in the data file too):

Please Login or Register  to view this content.

If anyone can find the problem (and preferably offer a solution!) I would be so grateful!

Cheers,

Bun

EDIT: Maybe I should add that D3 and G3 are the Solver Variables and Target Cell, respectively. In fact, it looks like it is possible to output the G3 values OK, but not the ones I want in D3!

[snb]

You must have overwritten the formula in D3, because it doesn't contain a formula.

Furthermore this macro will suffice:

Please Login or Register  to view this content.

[BunInJapan]

Hi snb,

Thanks but, as far as I know, it doesn`t need to (it never has before anyway!).

Copying and pasting new data into the file and using the Solver shows that it can be updated correctly.

Bun

[snb]

No it doesn't: the value in D3 doesn't change, because it's a fixed number. Please check the files you posted.

Are you mixing up D3 with another cell e.g. G3 ?

[BunInJapan]

Wow! Thanks snb!

That code is sooo concise and works perfectly for the first dataset (Columns B and C) and it is great to see the correct value carried over to the GPa results sheet too! But every cell in the results column (C3:C63) contains the same value now!

The macro is not taking the next dataset (Columns D and E) from the data and putting them into the workbook with the Solver. And then the next (Cols F and G) etc...

I imagine it is a matter of some simple recoding but for a newbie...! If you could refine a little...please!!

Cheers

Bun

[snb]

Alas, you didn't check my remarks.
The code works for all 60 datasets.
However you can't notice it because it copies D3 that has always the same value sixty times.
Use the macro below to see what I mean: it copies the result in G3 to the resultsheet.

Please Login or Register  to view this content.

[BunInJapan]

Sorry snb, but I cannot understand how it can be anything other than just a number...but maybe I am missing something. And I am certain that you do understand this better than me!

My thinking: the value of D3 is changed by Solver until the formulae in D8:D32 (these formulae include the D3 value) give minimum values of the difference between C8:C32 (data) and D8:D32. The equations for the squares of the difference are in column G8:G32. G3 is the sum of these values.

If I manually copy from the dataset workbook (e.g., columns D and E) and run the Solver, the value of D3 does change. It is not the same value as for columns B and C (because the data is different). So it is the result of these changes that I want to record.

If I can change D3 to a suitable formula without upsetting the Solver process, then great! But I cannot see how...

Cheers once more,

Bun

[snb]

It's a constant: D3 doesn't change in the file you posted. Maybe you removed a macro that was part of the file before you sent it.

D3 is being used as a constant in the formulae in column D and in cell J2.
There's no relation/connection between the computational results in columns D, E, F and G and other cell in the worksheet. So the copying of any cell outside these columns will result in sixty times the same value.

See the macro that is part of this file:
You'll notice that in that macro the cell M3 is being copied to the resultsheet.

[BunInJapan]

OK, well thanks for all that you did on the coding front...I will have a rethink about that D3 issue.

I just don`t want to have to go back to manually copying and pasting and Solving this stuff - I average 20,000 datasets each time!

Cheers again,

Bun