insert array formula into range based on option button selection

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

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]

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

hi xld,

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

thanks

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

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