insert array formula into range based on option button selection

[Anomandaris]

Hi,
I'm trying to plug in array formulae in some columns depending on the option selected from an option box.
Lets say in Sheet 1, I have an option box with 3 option buttons.....
Select option 1.
Then in Sheet2 I would want it to

insert 'option(3mx5y, ccy=eur, calc=bpvol)' into cell E7
insert the array forumula into E8 --- 'DQExcelAddin.Functions.DQ_GET_SERIES(B7,B8,E7,B5,"NO_DATES",B10) ' ---this formula should be there for the range E8:E200.

insert 'option(6mx5y, ccy=eur, calc=bpvol)' into cell F7
insert the array forumula into F8 --- 'DQExcelAddin.Functions.DQ_GET_SERIES(B7,B8,F7,B5,"NO_DATES",B10) ' ---this formula should be there for the range F8:F200.


Select option2.
Then in Sheet2- again i would insert a similar set of formulae on those same cells E7,E8,F7,F8

What the array formula does is import Data into excel
I only did 2 columns here E and F but actually will have to do it for 64 columns


any help would be appreciated
thanks

[Bob Phillips]

Something to get you started

[vba]

With Worksheets("Sheet2")

.Range("E7").Formula = "=option(3mx5y, ccy=eur, calc=bpvol)"
.Range("E8:E200").FormulaArray = "=DQExcelAddin.Functions.DQ_GET_SERIES(B7,B8,E7,B5,"NO_DATES",B10)"

.Range("E7").Formula = "=option(6mx5y, ccy=eur, calc=bpvol)"
.Range("F8:F200").FormulaArray = "=DQExcelAddin.Functions.DQ_GET_SERIES(B7,B8,F7,B5,"NO_DATES",B10)"
End With
[/vba]

[Anomandaris]

thanks xld,

I'll get started with it as ive got 64 columns to do that for...and thats just for 1 set of data, there's 20....

[Anomandaris]

hi xld,

the Formula Array line gives me a syntax error...and is highlighted in red.
any suggestions?

thanks

[Anomandaris]

anyone have any idea why the formulaArray doesnt work, I know it must be something on the right side of the = sign but not sure......

thanks

[Aussiebear]

Can't tell because you haven't posted a workbook to see...