Macro to replace two-variable data table

CENAK

New Member
Joined
Oct 15, 2004
Messages
41
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Thanks a bunch, Dave. Had to smile about your location as I am currently in Perth on business. Cheers and thanks. Andreas
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Andreas,

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

Cheers

Dave
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top