Sensitivity Analysis

  • Thread starter Thread starter RK
  • Start date Start date
R

RK

I need a macro to vary my sales forecast (which are values contained in a
range named "Forecast") by the factor specified in Cell A1. I've written
this macro:-

Sub Vary()
Dim cell As Range
For Each cell In Range("Forecast")
cell.Value = cell.Value * Range("A1")
Next
End Sub

Although this macro does vary the forecasts by the factor specified in cell
A1, the results are all values. I need each cell within the range "Forecast"
to actually contain a reference to cell A1 (i.e. *A1). In future, I can vary
the forecast by just changing the figure in cell A1 without running any
macro.

Thanks!
 
Hi
change the line
cell.Value = cell.Value * Range("A1")

to
cell.formula = "=$A$1*" & cell.value
 
Forget the macro.

With your data in cell A1, in any other blank cell, put

=$A$1

Now copy that cell, select your Forecast range and do Edit / Paste Special /
Multiply. They will all now contain a reference that looks like *($A$1).
Varying the value in A1 will trundle on through to every cell in your range.
 
Ken said:
Forget the macro.

With your data in cell A1, in any other blank cell, put

=$A$1

Now copy that cell, select your Forecast range and do Edit / Paste
Special / Multiply. They will all now contain a reference that looks
like *($A$1). Varying the value in A1 will trundle on through to
every cell in your range.
Click to expand...

Ken
nice one :-)
 
Thanks Frank/Ken! I was actually using a method without macros (but a much
more arduous method than that suggested by Ken).
 
LOL - I did it by accident one day, screwing up what I had really intended to
do, and thought what the hell have I done - then figured I could probably use
it. Have found it quite useful a number of times since, and usually for exactly
this kind of scenario.
 
Back
Top