i am trying to do a two way data table ... ouput=irr, column inputs = gearing, row inputs = acquistion price ... however i need to include a macro to calculate each option ... any ideas ... thx
Re: use of application.run & filenames
actually it was a school boy level solution at the end ... worked it out my self use the phrase run then the macro name in inverted commas i.e ... #
in place of #
... sorry to have raised an issues thats so simple!! ... in hindsight
thanks to you all anyway
i use a piece of code from the XL Logic site on most of my models as
scenario/sensitivity analysis ... linking into other macros which
perform sub routines using the style : Application.Run "'template.xls'!integratedrecalcscen"
How can i change this code so i do not have keep writing in the file
name for each version and /or when other people use the model they
dont cause problems when they change the filename ... i look forward to your
advice shortly ... many thanksCode
#Sub Scenario() Dim Scen_Range As Range, cell As Range, Output_Range As Range, Sel_Range As Range Set Scen_Range = Intersect(Range("VBA_ActiveScen").EntireRow, Range("k:IV")).SpecialCells(xlConstants) Act_Scen = Range("VBA_ActiveScen").Value For Each cell In Scen_Range Range("VBA_ActiveScen").Value = cell.Value Application.Run "'template.xls'!integratedrecalcscen" Set Output_Range = Intersect(cell.EntireColumn, Range ("VBA_Results").EntireRow) Output_Range.Select Output_Range.Value = Range("VBA_Results").Value Next cell Range("VBA_ActiveScen").Value = Act_Scen Set Scen_Range = Nothing Set Output_Range = Nothing Application.Run "'template.xls'!integratedrecalc" Sheets("scen").Select End Sub
I use names for formulas /arrays on a regular basis ... however the name box in the tool bar ( as standard ) always seems to small to refer to the title or to use as an index .... is there a way of enlarging the name box either from excel settings or by writing a VBA macro?