none
Range.Formula not set correctly when filters are applied on sheet RRS feed

  • Question

  • I am using C# for excel add in development and a strange issue is occurring. I am populating the formulas inside the cells as follows:

    private void SetFormula(Range range, string[,] formulaMatrix)
    {
    range.Formula = formulaMatrix;
    }

    This code works fine if no filter is there in the sheet. But if any column (in the given range) has a filter set, then many rows are duplicated with the same value. So for instance, say that formula matrix contains the following:

    [0,0] = "A"

    [1,0] = "B"

    [2,0] = "C"

    [3,0] = "D"

    [4,0] = "E"

    Then, from 3rd row onwards, the value set is "B" only. (specifically 3rd because the next filtered row is 3rd in number)

    I searched online about the problem but couldn't find anything.
    Tuesday, December 16, 2014 1:11 PM

All replies

  • Hi Harsh,

    Base on my test in VS2013 with Excel 2013, it works fine.

    These are steps:

    1. Code:
    private void button2_Click(object sender, EventArgs e)
            {
                string[,] s=new string[4,1];
                s[0,0]="A";
                s[1,0]="B";
                s[2,0]="C";
                s[3,0]="D";
                SetFormula(this.get_Range("A1", "A4"), s);
            }
            private void SetFormula(Excel.Range range, string[,] formulaMatrix)
            {
                range.Formula = formulaMatrix;
            }

    1. Click the button to trigger that event, works fine
    2. Clear content (A1:A4)
    3. Apply filter to column A (Data=>Filter)
    4. Click the button to trigger that event, works fine

    Could you share a simple project that can reproduce that issue on the OneDrive?  We will check it.

    Best Regards

    Starain


    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, December 17, 2014 8:50 AM
    Moderator
  • Hi,

    Thanks for the reply and sorry for the delayed response. I have created a sample project to depict the problem: Download Sample

    Because of the complexity of the project I couldn't replicate the exact scenario but it shows similar symptoms. Here are the steps to replicate it:

    1. Run the project. Excel should open up automatically

    2. Open FilterProblem.xlsx (It is inside the archive)

    3. Go to addins tab and click on "Set Data" button. The 1st column should be filled with some values.

    4. Notice that filter applied is to show only "2" and "3" values but the data includes other values too.

    5. Also, when the filter is removed it doesn't bring back the correct data. From the code (Controller.cs) it can be seen that the actual data set is different from what is shown and some values are duplicated.

    In the actual production application, behavior is very similar except for the fact that filtering doesn't need to be removed to see duplicated and incorrect values.

    Please let me know if anything needs clarity.

    Regards

    Harsh

    Tuesday, December 23, 2014 11:56 AM
  • Hi Harsh,

    Base on my test with your project, I can reproduce that issue.

    To deal with that issue, I suggest that you could specify the array to Range.FormulaArray property to achieve that.

    range.FormulaArray = formulaMatrix;

    Best Regards

    Starain


    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.

    • Marked as answer by Harsh Maurya Tuesday, December 30, 2014 4:22 AM
    • Unmarked as answer by Harsh Maurya Friday, April 24, 2015 10:09 AM
    Wednesday, December 24, 2014 2:20 AM
    Moderator
  • Hi Starain,

    Thanks a lot for the workaround. It is working. My only concern is whether this would have any issues if used for assigning single value instead of a matrix.

    string formula = "Sample";

    range.FormulaArray = formula;

    I need it to work because of the current code flow. From my testing it seems fine, but it is always better to have a second opinion :)

    Regards

    Harsh

    Monday, December 29, 2014 5:23 AM
  • After using and testing the workaround, I found that it is incredibly slow to use range.FormulaArray as compared to range.Formula. Currently, searching for a better way of doing this.
    Friday, January 16, 2015 11:33 AM