VBA help: need to transfer 130 cells to memory and back

[waeltken]

Hello,

I am very new to VBA and am trying to write a simple procedure which will "record" the formulas from 130 adjacent (dimensions: 1 row, 130 columns) to memory.

Then I want to multiply all the formulas by a factor, record the change in an output variable and finally replace the altered 130 cells with the original formulas.

I have figured out how to perform the multiplication, and get the readout values, but how do I assign 130 cells to a variable? Or do I need to use an array for this? Also, what is the syntax to assign and then retrieve the contents of these cells to "memory" (variable or array).

Please help!


-waeltken

 

[Joe4]

Welcome to the Board!

It may not be quite clear what you are after.
Can you provide a working example, what a cell formula starts out as, what you multiply by, what happens with the result, and what you want the end result to be?

Also, have you thought about using Excel cells as "temporary" cells for your 130 calculations, instead of trying to store them to memory?
Just another option...

 

[waeltken]

Hey,

so if I simply it this way, maybe it helps. Lets say I've got cells A3 through DX3. Each of them contains the relative formula SUM(A1:A2), SUM(B1:B2), SUM(C1:C2) etc.

the outcome of these formulas affect a single output cell further below in a discounted cash flow calculation.

Now the macro that I am trying to write should go into Row 3, collect all the original formulas contained in cells A3:DX3 in an Array or Variable (that's what I am trying clarify), multiply all formulas in the Range by a number (effectively adding the number to the formulas via PasteSpecial Operation: Multiply), then go to the output cell further down the spreadsheet and record the changed output value, write it somewhere else as a value only and lastly undo the change to the Range A3:DX3, effectively refilling the Range with the original formulas, as if nothing ever happened.

Does this make more sense?

 

[Norie]

If all you are doing is a simple multiplication and sum why do you need to store things in memory?

Don't you know all the formulas, the values they return, how many of them there are etc?

Why do you need to change the original range anyway?

You could just use a formula in another column(s) that references the values from the original range, copy the result of that, clear out the columns and repeat.

Or am I missing something?

 

[Joe4]

Yes, I was thinking along the same lines as Norie. I am not sure I understand the need to store all the calculations in memory instead of using other cells on your worksheet (or another worksheet) to do the calculations. If there some reason it can't be done this way?

 

[waeltken]

So, this is a nearly 100 MB large market simulation, all of which is data. The macro I am trying to write will simulate a 15%, 20%, 50% revenue increase which is summarized in 120 monthly cells - 10 years. there are about 140 sheets feeding input data into this summary sheet. It is however necessary that the macro simulates the increase in prices across 120 periods, reads out the resulting valuation increase, records the value as a static output somewhere and then returns everything to normal.

This is just one of 6 different scenarios which will be examined in iterations all of which will always be dependent on how some 700 or so input variables are set.

In this instance I have now learned I could insert a new worksheet, use it as a mirror of the original sheet and then insert the factor 1.15, 1.2 or 1.5 to get my increased valuation. In the next step I however have to simulate the opposite, a 15%, 20%, 50% decrease in revenue.

Would be interesting to know how to commit the formulas of 120 cells to memory anyway. Do I have to use an array for that?

 

[Norie]

If you want to simulate 15%, 20%, 50% decrease use the factors 0.85, 0.8 and 0.5.

I'm still not sure what need you have to commit formulas to memory, in fact I'm not 100% sure what you mean by that.

If you are using code then you can create an object variable that references an entire range like this.

Set rng = Range("A1:A100)

You can access the properties of this range and each individual cell within it, including formulas.