none
Autofill doesn't work as expected RRS feed

  • 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

    Tuesday, November 4, 2014 3:10 PM

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
    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\","+(low0-1)+",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.

    Wednesday, November 5, 2014 8:35 AM
    Moderator
  • 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\","+(low0-1)+",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\","+(low0-1)+",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

    Wednesday, November 5, 2014 1:41 PM
  • 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
    Thursday, December 18, 2014 8:49 AM