Answered by:
Autofill doesn't work as expected
Question

Hello,
i use this C# code
Excel.Range rng = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 21, 1, 1); Excel.Range dest = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 30, 1, 1); object[,] val = rng.Formula; int low0 = val.GetLowerBound(0), low1 = val.GetLowerBound(1); val[low0, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))"; val[low0 + 1, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))"; rng.Formula = val; ConnectHelper.setEnUSCulture(); rng.AutoFill(dest); ConnectHelper.setEnUSCulture(); val = dest.Formula;
What I wanted to have is
val {object[1..11, 1..1]} object[,] [1, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [2, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [3, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",2,4,1))" object {string} [4, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",3,4,1))" object {string} [5, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",4,4,1))" object {string} [6, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",5,4,1))" object {string} [7, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",6,4,1))" object {string} [8, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",7,4,1))" object {string} [9, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",9,4,1))" object {string} [10, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",9,4,1))" object {string} [11, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",10,4,1))" object {string}
but he best I get is
val {object[1..11, 1..1]} object[,] [1, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [2, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [3, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [4, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [5, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [6, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [7, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [8, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [9, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [10, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string} [11, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string}
How must I change my code to get the desired result ?
TIA
Hendrik Schmieder
Answers

I solved the my Problem with using
"=MyUDF1($A$17,MyUDF2($A$12,\"Years\",Row()Row(A$20),4,1))";
Hendrik Schmieder Marked as answer by h_schmieder Thursday, December 18, 2014 8:49 AM
All replies

Hi Hendrik,
You can change the formula string like this:
val[low0, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\","+(low01)+",4,1))";
You set a constant string value to the formula string, that's why you always got the same formula.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. 
This doesn't change anything.
Excel.Range rng = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 21, 1, 1); Excel.Range dest = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 30, 1, 1); object[,] val = rng.Formula; int low0 = val.GetLowerBound(0), low1 = val.GetLowerBound(1); val[low0, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\","+(low01)+",4,1))"; val[low0 + 1, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\","+low0+",4,1))"; rng.Formula = val; ConnectHelper.setEnUSCulture(); rng.AutoFill(dest); ConnectHelper.setEnUSCulture(); val = dest.Formula; Excel.Range rng = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 21, 1, 1); Excel.Range dest = ExcelHelper.Create_Range(ExcelHelper.ActiveWorksheet, 20, 30, 1, 1); object[,] val = rng.Formula; int low0 = val.GetLowerBound(0), low1 = val.GetLowerBound(1); val[low0, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\","+(low01)+",4,1))"; val[low0 + 1, low1] = "=MyUDF1($A$17,MyUDF2($A$12,\"Years\","+low0+",4,1))"; rng.Formula = val; ConnectHelper.setEnUSCulture(); rng.AutoFill(dest); ConnectHelper.setEnUSCulture(); val = dest.Formula;
before autofill I have
rng.Formula {object[1..2, 1..1]} dynamic {object[,]} [1, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",0,4,1))" object {string} [2, 1] "=MyUDF1($A$17,MyUDF2($A$12,\"Years\",1,4,1))" object {string}
That's the same I got with my original code and so I get the same result after autofill.
Both formulas differ in exactly one place.
Hendrik Schmieder

I solved the my Problem with using
"=MyUDF1($A$17,MyUDF2($A$12,\"Years\",Row()Row(A$20),4,1))";
Hendrik Schmieder Marked as answer by h_schmieder Thursday, December 18, 2014 8:49 AM