Create scenarios using macro and data in cells

[SterlingA]

Is there a way to take the data in cells to be the input values for scenarios?

meaning let's say i have 2 variables I would like to enter the values in column A and Column B and then use a macro or some method to automatically create the scenarios using that data.

any suggestions?

thanks in advance.

[anilsolipuram]

method to automatically create the scenarios, what do you mean by scenarios?

[SterlingA]

I want to create the scenarios in scenario manager, but do it automatically with a macro rather than entering them one by one. Especially in cases where I have 15-20.

For example,

hypothetically let's say the cells to change are A1 and B1 and result cell is C1.

I would like to put data in A2:A7 for one variable and B2:B7 for the second variable.

let's say the result sell is simply the sum of A1 and B1.

So basically, can I use a macro to take the data in A2:A7 and B2:B7 and create the six scenarios

thanks

[anilsolipuram]

I am not sure if this is what you are looking for.

right click sheet tab->view code, and paste this code


whenever you change any value at a2:a7 or b2:b7 , c2:c7 will automatically sum a and b cells

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t As Range
Set t = Application.Intersect(Range("$A$1:$B$7"), Target)
If t Is Nothing Then
Else
For Each C In Range("A2:A7")
TEMP = C.Offset(0, 1).Value + C.Value
If TEMP <> 0 Then
C.Offset(0, 2).Value = TEMP
End If
Next
End If
End Sub

[abcd]

if you mean "adding a scenario in the list of excel scenarios" using a
macro for that:

start the recorder in the macro menu
create a scenario
stop the recording
go in VBA editor to see the syntax
re-use it as you wish

IF you mean "doing the same job than a scenario, whithout using a
scenario" then... just write a macro ! ;-)

For anilso... : a scenario is a pre-fetched data in cells so you can
change "initial data" on a sheet and then compare methods, prices, or
whatever... You change input data depending on a known "scenario"

[abcd]

well, ok no problem

then... just use the way i said

open macro / new macro
to record all what you will do

add a scenario the usual way (don't care wich one)

stop the recording of the macro recording

and then go in the VBA macro editor
you'll see a command like
ActiveSheet.Scenarios.Add etc.

here you see the syntax (how to add a scenario in vba)


then ... the question is: do you really know how to write a macro ?
maybe you do not just need one syntax. Tell us your level,
and also , please, try to "try said things".

[abcd]

Sub CreateScenario()
Dim line&, thename$, thevalues
Const thecells$ = "R1C1:R1C2" 'this mean "A1:B1" in RC syntax
' R1C1 means Row 1 and Column 1

For line = 2 To 4 'data rows to be added
thename = "scenario" & line
thevalues = Array(CStr(Cells(line, 1).Value), _
CStr(Cells(line, 2).Value))
' cells( row number , column number)

ActiveSheet.Scenarios.Add name:=thename, _
ChangingCells:=thecells, _
values:=thevalues, _
Comment:="macro created", _
Locked:=False, Hidden:=False

Next line
End Sub

[SterlingA]

Thanks for the help. Using what you gave me this was what I finished with. So I have a block of input, first column holds scenario name, second column has data for the first variable, and third column the data for third variable. You start the macro with the cursor in the first row on the first scenario name. Macro captures the scenario name, then the values using ActiveCell.Offset. I have a lot of hidden columns so that is why the 5 and 10 for offsets. The Do loop keeps the macro going until it hits an empty cell signifying the end of the scenarios.

pasted the code in case anyone else could use it after modifying to their own needs.

Thanks again for the help.

Sub CreateScenario()
Dim line&, thename$, thevalues

Do
thename = ActiveCell.Value
thevalues = Array(CStr(ActiveCell.Offset(0, 5).Value), CStr(ActiveCell.Offset(0, 10).Value))

ActiveSheet.Scenarios.Add Name:=thename, _
ChangingCells:=Range("D77,D39"), _
Values:=thevalues, _
Locked:=False, Hidden:=False
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell)
End Sub