How to simulate Datatable behaviour

[kayard]

Hi to everyone,

I'm quite new to VBA so perhaps the best way to explain this is by describing what I would like to do. I have a sheet where 4 cells (A1:B2) provides outputs depending on the value of 1 input cell (C3).
The value of this input cell can be one of many (maybe even 1000 different integer values) I need to have in cells (D1:E2) the sum of every values that I have in A1:B2 while changing the input cell (C3). So in cell D1 I would have the sum of all values I get in A1 while changing C3 from 1 to 1000 (for instance). In E1 the same but summing B1 while changing C3, in D2 summing A2 and so on.

I need to do this with VBA because the real case is much more complicated than this and cannot be easily solved whit excel builtin menu-driven datatable function.

Basically I need to know how to get in vba the value from A1 while changing C3 and having the sheet recalculated

I hope to have made myself understood and sorry for my english.

Thanks to anyone who can help

Paolo

Italy

[boylejob]

Paolo,

I have attached a workbook that hopefully does what you state in your example and will get you started.

In cells A1:B2 there are formulas that calculate a number based on the value entered into C3. Each time a value is entered into C3 the new numbers are calculated in A1:B2 are added to D1:E2 where a running total is maintained.

The code is being maintained in the sheet1 object and fires each time the value in C3 is changed. Try this out and if you need more help let me know and I will see what I can do.

[kayard]

First of all thank you very much for your prompt answer.

Unfortunately this isn't what I really needed and this is to blame on my english ...

I'll try to explain it in using a english-like programming language sintax.

Say that I have a sheet modelling some physic experiment where a lot of outputs are calculated by very complex formulas and all outcomes depends on a single input cell that might be, for example, room temperature. I would like to have the sheet recalculated for every input value I have in a ranged name INPUTS (1 row or 1 column only) and the values from the output cell retrieved, summed together, and put somewhere else.

So In english the algorhytm would be

With the previous example as a reference:

C3 Input cell
A1:B2 Output cells with complicated formulas
D1:E2 Where I have to put the results of my simulation
INPUTS is the named range where i have tons of different values that have to be put, one by one, in C3 and used as an input database.

algorhytm:

for each VALUE in INPUTS
put VALUE in C3
recalculate sheet
take values in A1,B1,A2,B2 and store them in an array
repeat the for loop until all values in INPUTS have been used for calculation and corresponding outputs in A1,B1,A2,B2 are all stored somehow
sum all A1 and put sum in D1
sum all B1 and put sum in E1
sum all A2 and put sum in D2
sum all B2 and put sum in E2
end

I hope this explanation helps you understand what I'm trying to do !

Thanks in advance for any support

Paolo

Italy

[boylejob]

Paolo,

See if this is any better. I am using three spreadsheets; Calc, Input, and Results.

Calc will contain the formula.
Input will contain your input values.
Results will house the results from the calculations done on each input value.

It will also maintain a running total of the values and will put those totals on the Calc sheet once all input values have been run.

Everything is running off of a subroutine called Calc.

Hopefully, I understood what you were explaining and this will get you a little closer to the results you are wanting. Of course you may need to make modifications to meet your exact needs. If not, let me know and I will continue to try to help.