none
Iterating through range skips rows

    Question

  • I am looping through the rows in a named range control and adding two cells to a Dictionary

    after the second iteration it seems to skip two rows

    itemsRT is just for debugging it returns $6:$10 which is the range of the named range contrl

    tsr is the same just to get the address of the row $6:$6,$7:$7 etc.

    tval is to correct for range the range starts on row 6 so to iterate throught the rows you want [1,column], [2,column] etc.

    fred is just for debugging purposes.

    the first time through it returns "001" and an amount  Cells[1,3]=001 and Cells[1,8] = amount
    second time through it returns "101" and an amount Cells[2,3]=101 and Cells[2,8] = amount

    the third time through it returns "104" and an amount having skipped two lines Cells[3,3]=102 and an different amount in cells[3,8]

    retried this numerous times any ideas, full code below


    internal

     

    void GetAllForCLIN(Dictionary<string,double> clins)

    {

     

    //Excel.Range items = Globals.Sheet4.labor_item.RefersToRange;

     

    string itemsRT = Globals.Sheet4.labor_item.RefersTo;

     

     

    double val = 0.0;

     

    for (int i = 1; i < Globals.Sheet4.labor_item.RefersToRange.Rows.Count; i++)

    {

    Excel.

    Range rng = Globals.Sheet4.labor_item.RefersToRange.Rows[i, missing].ToRange();

     

    string tstr = rng.get_Address(Excel.XlReferenceStyle.xlA1);

     

    int tval = rng.Row - 5;

     

    object fred = rng.Cells[tval, 8].ToRange().Value2;

    val =

    Convert.ToDouble(fred);

     

    string key = Convert.ToString((rng.Cells[tval, 3] as Excel.Range).Value2);

     

    if (clins.Keys.Contains(key))

    {

    clins[key] = clins[key] + val;

    }

     

    else

    {

    clins.Add(key, val);

    }

     

    }

    }

    Wednesday, March 17, 2010 6:32 PM

Answers

  • Checking why your code skips rows, you will see that

    Range rng = Globals .Sheet4.labor_item.RefersToRange.Rows[i, missing].ToRange(); 
    returns you i'th row in the named range

    and then
    string key = Convert .ToString((rng.Cells[tval, 3] as Excel. Range ).Value2);

    returns you the i'th row from the i'th row in the named Range, you might have changed your code to read
    string key = Convert .ToString((rng.Cells[1, 3] as Excel. Range ).Value2);

    to get the correct key.

    • Marked as answer by mgbee110 Thursday, March 18, 2010 4:48 PM
    Thursday, March 18, 2010 8:24 AM

All replies

  • Hi,


    I have reworked the example as follows

            internal void GetAllForCLIN(Dictionary<string, double> clins)
            {
    
                double val = 0.0;            
                double valFromRange;
                object[,] data = Globals.Sheet4.labor_item.Value2 as object[,];
    
                for (int i = 1; i <= data.GetUpperBound(0); i++)
                {
    
                    string key = Convert.ToString(data[i, 3]);
                    valFromRange = Convert.ToDouble(data[i, 8]);
    
                    if (clins.TryGetValue(key, out val))
                    {
                        clins[key] += valFromRange;
    
                    }
                    else
                    {
                        clins.Add(key, valFromRange);
                    }
    
                }

    the use of the object[,] is to avoid all the crossing between com and managed. It's significant when there is lots of data.

    Regards
    Thursday, March 18, 2010 8:03 AM
  • Checking why your code skips rows, you will see that

    Range rng = Globals .Sheet4.labor_item.RefersToRange.Rows[i, missing].ToRange(); 
    returns you i'th row in the named range

    and then
    string key = Convert .ToString((rng.Cells[tval, 3] as Excel. Range ).Value2);

    returns you the i'th row from the i'th row in the named Range, you might have changed your code to read
    string key = Convert .ToString((rng.Cells[1, 3] as Excel. Range ).Value2);

    to get the correct key.

    • Marked as answer by mgbee110 Thursday, March 18, 2010 4:48 PM
    Thursday, March 18, 2010 8:24 AM