# 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();

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

{

}

}

}

Wednesday, March 17, 2010 6:32 PM

• 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 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
{
}

}```

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 Thursday, March 18, 2010 4:48 PM
Thursday, March 18, 2010 8:24 AM