Macro to replace two-variable data table

[CENAK]

I would like to replace a two-variable data table with a macro. The reason is that the calculation of the table takes a lot of time and is not always needed when I calculate. In other words, my calculation is set to automatic except tables already, but stll the calculation of the table takes long and is not always necessary. The table also calculates everytime I run the other macros and it just takes forever.

How can I use a Macro to replace the data table? I have app. 15 input variables on the x-axis and app. 10 on the y-axis, so a total of 150 output calculations in the table.

Thanks.

 

[brettdj]

I've written code that should help with this. The code:
1) Maps and deletes data tables (one-way and two-way)
2) Restores the deleted data tables

You can download the code (+ example file) @ http://vbaexpress.com/kb/getarticle.php?kb_id=453

Cheers

Dave

 

[CENAK]

Thanks a bunch, Dave. Had to smile about your location as I am currently in Perth on business. Cheers and thanks. Andreas

 

[CENAK]

Dave,

I copied the macro into my file and it seems to work up to a certain point.

The delete part works fine.

The restore part works fine only to a certain extent. I have a two dimensional data table that I restore. Once restored, the formula in every cell is actually the correct data table formula just as before the delete, however only in the first row the calculated values are correct. In the second and following rows, the values shown in the data table are the column input value of the previous row. Let say my row 3 is supposed to calculate the data table based on a column input value of 150, then row 4 shows 150 throughout the entire row.

I switched rows and columns but the problem remains the same i.e., the first row calculates correctly and the second row shows the column input value of the first row etc.

This is most lilely user error but I wanted to know what I am doing wrong here. Thanks for your help.

Andreas

 

[brettdj]

Andreas,

Sorry for the delay, my home pc has been at the repairers for the last two weeks

I'm replicating your issue - the data table is re-created correctly by the code, but it is refusing to update rows after row 1. A manual re-create works fine

So I'm currently trying to establish whether this is a local workbook issue, or whether your convergence macro is somehow interfering

Cheers

Dave

 

[CENAK]

Dave,

No problem. I am just grateful that you are taking the time to help.

Andreas

 

[brettdj]

Hi Andreas,

I couldn't find a code approach to properly re-establish the data table. I also tried rebuilding the workbook in case there was a corruption issue but I had no luck

Sorry I can't help further

Interesting file though - I work in major project valuation in my day job

Cheers

Dave

 

[CENAK]

Dave,

No sweat and thanks. I was able to make the table smaller with less input variables and the wait is not that bad any more. At some point I may try to mess with your macro again because I know this problem will come up again.

Please feel free to use elements from the model, but please don't forward the entire file. My general counsel would kill me

AK

 

[brettdj]

Andreas,

FYI, This is a repeatable problem

The code works fine on rebuilding a column table (one way), or a row and column table (two way).

But the code will not refresh rows 2 onwards of a row table (one way). So while the code is helpful as a marker of where the manual remap needs to go, it won't do the full job by itself

I haven't picked this up before as for some reason I've never bothered with one way row data tables

A strange excel glitch, it will be interesting to see if this works in Excel 12

Cheers

Dave

 

[brettdj]

Andreas,

I have managed to overcome this problem, PM or email me if you want the updated code

Cheers

Dave