How to iterate on Excel.Range with an index in c#

Question

I'm writing a c# Add in for excel that takes values from a column of an excel sheet and does some operations. This is a part of the code that I wrote:

        Excel.Range aqRange = currentSheet.Range["C3:C" + cRowCount];
        Excel.Range vcRange = currentSheet.Range["D3:D" + cRowCount];
        Excel.Range k0R = currentSheet.Range["F2"];
        double[] sgVett = new double[cRowCount];

        foreach (Range aq in aqRange)
        {
            if (i == 0) sgVett[i] = k0R.Value + aq.Value;
            else sgVett[i] = sgVett[i - 1] + aq.Value;
            i++;
        }
        i = 0;

        foreach (Range vc in vcRange)
        {
            if (i == 0) sgVett[i] -= vc.Value;
            else sgVett[i] -= vc.Value;
            i++;
        }

I want to know if is there some way to do something like this:

for(int j = 0; j<cRowCount; j++) {
    if (i == 0) sgVett[i] = k0R.Value + aqRange[i].Value;
    else sgVett[i] = sgVett[i - 1] + aqRange[i].Value;
}

I know that I can't use a Excel.Range as a vector, but is there some way to use an index to scroll aqRange and vcRange or, at least, if it's possible to use just one for or one foreach instead of two (as I done).

PaulF · Accepted Answer · 2019-03-04T12:02:21.977

You can use the Range's Cells property a bit like a 2 dimensional array.

It is important to note that although the syntax used is similar to a C# 2D array (Cells[RowIndex, ColIndex]), the Cells property accesses a COM object that uses 1 as it's start offset, rather than 0.

So you should be able to write something like :

for(int j = 1; j<=cRowCount; j++)
{
    if (i == 0) sgVett[i] = k0R.Value + aqRange.Cells[j,1].Value;
}

Also see here : Fastest way to get an Excel Range of Rows & How do I get an Excel range using row and column numbers in VSTO / C#?

How to iterate on Excel.Range with an index in c#

1 Answers1