Hi,

I have a very large large spreadsheet (Excel 2003) with 20000 rows and
about 50 columns most with long complex formulas so calculation on
this sheet takes time. The main purpose of my code is to perform a
Goal Seek but depending on results the code might loop up to 100
times. Each Goal Seek takes about 9 minutes!

One column from the table changes at each iteration so what I want to
do is copy this column to another workbook, perform the Goal Seek,
then copy back my required result. My problem is though that if the
second workbook is in the same Excel instance as the actual table the
process is just as slow.

So my question is, how do I access a completely separate instance
(instance 2) within my VBA code so as to paste the 20000 line column,
perform the goal seek in that workbook then copy the result i require
back into instance 1

Any help appreciated

...pc

Hi

Post your code, maybe it can be improved to run more efficient.

If you isn't turning off screenupdating by your macro, then add this line as
one of the first lines.

Application.ScreenUpdating = False

and remember to set =True before End Sub

Regards,
Per

"PMC1" skrev i meddelelsen
...
Hi,

I have a very large large spreadsheet (Excel 2003) with 20000 rows and
about 50 columns most with long complex formulas so calculation on
this sheet takes time. The main purpose of my code is to perform a
Goal Seek but depending on results the code might loop up to 100
times. Each Goal Seek takes about 9 minutes!

One column from the table changes at each iteration so what I want to
do is copy this column to another workbook, perform the Goal Seek,
then copy back my required result. My problem is though that if the
second workbook is in the same Excel instance as the actual table the
process is just as slow.

So my question is, how do I access a completely separate instance
(instance 2) within my VBA code so as to paste the 20000 line column,
perform the goal seek in that workbook then copy the result i require
back into instance 1

Any help appreciated

..pc

On Sep 11, 12:40*pm, "Per Jessen" wrote:
Hi

Post your code, maybe it can be improved to run more efficient.

If you isn't turning off screenupdating by your macro, then add this line as
one of the first lines.

Application.ScreenUpdating = False

and remember to set =True before End Sub

Regards,
Per

"PMC1" skrev i ...

Hi,

I have a very large large spreadsheet (Excel 2003) with 20000 rows and
about 50 columns most with long complex formulas so calculation on
this sheet takes time. The main purpose of my code is to perform a
Goal Seek but depending on results the code might loop up to 100
times. Each Goal Seek takes about 9 minutes!

One column from the table changes at each iteration so what I want to
do is copy this column to another workbook, perform the Goal Seek,
then copy back my required result. My problem is though that if the
second workbook is in the same Excel instance as the actual table the
process is just as slow.

So my question is, how do I access a completely separate instance
(instance 2) within my VBA code so as to paste the 20000 line column,
perform the goal seek in that workbook then copy the result i require
back into instance 1

Any help appreciated

..pc

Hi Per,

I've debuged the code (of which there is over 200 lines) as much as I
can. I know for a fact that it is one line that is causing all the
delay:

wksGSCalc.Range("C1").GoalSeek Goal:=0, ChangingCell:=wksGSCalc.Range
("C2")

C1 contains a formula =SUMPRODUCT(C11:C22615,D11:D22615)
C2 also contains a formula =1/((1+$C$2)^(([CHANGES EVERY CELL]/
365.25))

The problem is that as the goal seek iterates through all the
possibilities all the while changeing the value of C2 this is causing
the values in 20000 cells to change also.

Its not important where the Goal Seek occurs, what is important is the
result in cell C2. So I want to copy Columns C and D to another
workbook Instance (instance is important because copying to another
workbook in the same instance doesn't seem to improve performance),
perform the goal seek in the other instance, then copy the result in
cell C2 back to the first instance.

BTW - I have screen updating and automatic calculation turned off

Thanks

...pc