C# excel range.FormulaArray not working -

C# excel range.FormulaArray not working -

here c# code write arrayformula cell in excel. using uft (unified functional testing) uses c# custom code.

string sheetname = "xyz"; string wsmethodname = "abc"; int = 2; excel.application xlapp = null; xlapp = new excel.applicationclass(); wb = xlapp.workbooks.open(srcfile, 0, false, 5, "", "", false, excel.xlplatform.xlwindows, "", true, false, 0, true, false, false); worksheet = (excel.worksheet)wb.worksheets[sheetname]; excel.range excelcell = (excel.range)worksheet.get_range("b2", "b21"); foreach (excel.range c in excelcell) { // stravgformula = "=averageifs(" + "(offset(\'" + sheetname + "\'!$a$1,2,2,counta(\'" + sheetname + "\'!$a:$a)-2,1))," + "offset(\'" + sheetname + "\'!$a$1,2,16382,counta(\'" + sheetname + "\'!$a:$a)-2,1)," + "(mid(c" + + ",1,(find(\"-\",c" + + "))-2))," + "offset(\'" + sheetname + "\'!$a$1,2,16383,counta(\'" + sheetname + "\'!$a:$a)-2,1)," + "(mid(c" + + ",(find(\"-\",c" + + ")+1),(find(\"/\",c" + + "))-(find(\"-\",c" + + ")+1))))"; this.codeactivity16.report("stravgformula",stravgformula); // strmaxformula = "=max(" + "if((offset(\'" + sheetname + "\'!$a$1,2,16382,counta(\'" + sheetname + "\'!$a:$a)-2,1)=mid(c" + + ",1,(find(\"-\",c" + + "))-2))*" + "(offset(\'" + sheetname + "\'!$a$1,2,16383,counta(\'" + sheetname + "\'!$a:$a)-2,1)=mid(c" + + ",(find(\"-\",c" + + ")+2)," + "(find(\"/\",c" + + "))-(find(\"-\",c" + + ")+2)))," + "offset(\'" + sheetname + "\'!$a$1,2,2,counta(\'" + sheetname + "\'!$a:$a)-2,1)))"; this.codeactivity16.report("strmaxformula",strmaxformula); if (c.value2.tostring() == wsmethodname) { newexcelcell = (excel.range)worksheet.get_range("f" + i, "f" + i); newexcelcell.clear(); newexcelcell.formulaarray = stravgformula; //failing @ line, error mentioned below //newexcelcell.value = stravgformula; newexcelcell = (excel.range)worksheet.get_range("g" + i, "g" + i); newexcelcell.clear(); newexcelcell.formulaarray = strmaxformula; //newexcelcell.value = strmaxformula; break; } ++; } wb.save(); xlapp.workbooks.close(); xlapp.quit(); releaseobject(newexcelcell); releaseobject(excelcell); releaseobject(worksheet); releaseobject(wb); releaseobject(xlapp); private void releaseobject(object obj) { seek { marshal.releasecomobject(obj); obj = null; } grab (exception ex) { obj = null; codeactivity16.report("error","unable release object " + ex.tostring()); } { gc.collect(); } }

now, if re-create same formula printed output result , paste in desired cell, working fine. escape characters doing job properly. if alter newexcelcell.formulaarray newexcelcell.value, writing excel works normal formula , not arrayformula (like ctrl + shift + enter).

here error getting result file: formula typed contains error. seek 1 of following: • create sure you've included parentheses , required arguments. • assistance using function, click function wizard on formulas tab (in function library group). • if include reference sheet or workbook, verify reference correct. • if not trying come in formula, avoid using equal sign (=) or minus sign (-), or precede single quotation mark ( ' ). • more info mutual formula problems, click help.

thanks in advance help or suggestion.

update:

here formula stravgformula trying write.

"=averageifs(" + "(offset('1'!$a$1,2,2,counta('1'!$a:$a)-2,1))," + "offset('1'!$a$1,2,16382,counta('1'!$a:$a)-2,1),(mid(c2,1,(find("-",c2))-2))," + "offset('1'!$a$1,2,16383,counta('1'!$a:$a)-2,1),(mid(c2,(find("-",c2)+1),(find("/",c2))-(find("-",c2)+1))))"

formula strmaxformula working fine.

so, got workaround issue.

it turned out (this feel) either excel not accepting formula string stravgformula or messed because of cell reference using offset.

i've utilize direct cell reference.

here working formula:

int lastusedrowdiffsheet = worksheet.cells.specialcells(excel.xlcelltype.xlcelltypelastcell,type.missing).row; stravgformula = "=averageifs('" + sheetname + "\'!c" + irowcount + ":c" + lastusedrowdiffsheet + "," + "'" + sheetname + "'!xfc" + irowcount + ":xfc" + lastusedrowdiffsheet + "," + "mid(c" + + ",1,(find(\"-\",c" + + "))-2)," + "'" + sheetname + "\'!xfd" + irowcount + ":xfd" + lastusedrowdiffsheet + "," + "(mid(c" + + ",(find(\"-\",c" + + ")+1),(find(\"/\",c" + + "))-(find(\"-\",c" + + ")+1))))";

c# excel excel-formula hp-uft