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.


--
SterlingA
------------------------------------------------------------------------
SterlingA's Profile: http://www.excelforum.com/member.php...o&userid=10913
View this thread: http://www.excelforum.com/showthread...hreadid=382252

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

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38225

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"

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


--
SterlingA
------------------------------------------------------------------------
SterlingA's Profile: http://www.excelforum.com/member.php...o&userid=10913
View this thread: http://www.excelforum.com/showthread...hreadid=382252

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=382252

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".

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

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


--
SterlingA
------------------------------------------------------------------------
SterlingA's Profile: http://www.excelforum.com/member.php...o&userid=10913
View this thread: http://www.excelforum.com/showthread...hreadid=382252