Excel formulas not calculating when copied from recordset -

i building formulas in sql query using excel vba function copyfromrecordset insert formulas in spreadsheet. text of formula enters correctly displays text , not calculate. calculation mode set automatic, , cell formatted "general" , not "text".

in order formulas calculate, have enter each cell if going edit formula , leave cell without having edit anything. action of entering , exiting causes formula calculate must done manually on each cell.

sub test1()   application.referencestyle = xlr1c1    dim qry string   qry = "select myear, '=rc[-1]*12' xformula tblyears"    dim datacmd adodb.command   set datacmd = new adodb.command    datacmd.commandtext = qry    dim datars adodb.recordset   set datars = db.runselect(gv.constr_config, datacmd)    activesheet.range("a1").copyfromrecordset datars    application.referencestyle = xla1 end sub 

this results in this...

    1       2 1 2012    =rc[-1]*12 2 2013    =rc[-1]*12 3 2014    =rc[-1]*12 

then have double click in each formula cell start edit, can click on cell exit formula , calculate.

    1       2 1 2012    24144 2 2013    24156 3 2014    24168 

i've tried adding calculate button; , adding vba command application.calculation=xlcalculationmanual @ beginning application.calculate (and calculatefull)at end.

i can't calculations in query, formulas need interactive excel data in final product.

(the db.runselect function method executing query , returning disconnected recordset.)

thanks

you have iterate resultset , set formulas this:

range("a1").formula = myformulastring 

or

range("a1").formular1c1 = myformulastring