Sign in to follow this  
Followers 0
Jfish

_ExcelWriteFormula question - can't get it to work

3 posts in this topic

I am having trouble getting the following formula to write to a cell using either excelwritecell or excelwriteforula. This is the formula I am trying to use:

dim $formula = "=SUMPRODUCT(--(ISNUMBER(SEARCH(G2,D2:72))))"

_ExcelWriteFormula($oExcel,$formula,2,8)

This is the error message I get:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula^ ERROR

Can anybody tell me why this won't work? Any help would be greatly appreciated.

- Jfish


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites



Okay, found the problem ... "=SUMPRODUCT(--(ISNUMBER(SEARCH(G2,D2:D72))))" was missing the D in front of 72.

Now I am trying to use variables for the range:

"=SUMPRODUCT(--(ISNUMBER(SEARCH(G"&$b+2&",D2:"&"D"&$totalRecords&"))))"

It writes to excel but the variables are surrounded in quotes that render the formula ineffective (from Excel):

=SUMPRODUCT(--(ISNUMBER(SEARCH('G6','D2':'D72'))))

How can I get rid of the quotes and still get it to use the variables?


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Okay, I finally got this to work ... sort of. I cannot get it to work with the _excelwriteformula function because it wants to insert additional single quotation marks around the variables, i.e. '

However, using the same string with _excelwritecell works! Is that a bug in the _excelwriteformula function?

There does appear to be a difference in the way they work - this was a source of confusion in:

http://www.autoitscript.com/forum/index.php?showtopic=75450&st=0&p=547843&hl=_excelwriteformula&fromsearch=1&#entry547843


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0