_ExcelWriteFormula question - can't get it to work

[Jfish]

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

[Jfish]

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?

[Jfish]

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