Sensitivity Analysis
 

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!

Sensitivity Analysis
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

RK wrote:
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!

Sensitivity Analysis
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"RK" wrote in message
...
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!




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004

Sensitivity Analysis
 

Ken Wright wrote:
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
nice one :-)

Sensitivity Analysis
 

Thanks Frank/Ken! I was actually using a method without macros (but a much
more arduous method than that suggested by Ken).

Sensitivity Analysis
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" wrote in message
...
Ken Wright wrote:
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
nice one :-)



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004