CODEWITHSUNDEEP
c#excelforeachvba
kPuck
kPuck

Reputation: 147

Move to next cell in Excel.Range in a foreach loop in C#

I have an excel.range called rng. I iterate through it using a foreach loop.

foreach(Excel.Range cell in rng)
{
   //do something
}

The foreach loop is very useful in my application and I cannot use a for loop. Now to my question:

Is there any way to move to the i:th cell relative to the current cell? And continue my foreach loop from that cell. Like this:

foreach(Excel.Range cell in rng)
{
    If(cell.value.ToString() == "Something")
       //move 3 cells forward in rng relative to the current cell
}

I'm not looking for the continue; expression. I need to move forward in my range somehow. How I do it is less important. It could be a loop using some kind of moveNext() command or some kind of indexing.

I appreciate any help I can get!

Upvotes: 0

Views: 2626

Answers (3)

Caio César S. Leonardi
Caio César S. Leonardi

Reputation: 536

if you just want to skip the foreach, in this case, you could do something like this:

int i = 0;
foreach(Excel.Range cell in rng)
{
  if(i>0)
  {
    i--;
  }
  else
  {
    if(cell.value.ToString() == "Something")
      i=3;
  }

with that you'd be "skipping" the foreach, but still, try to use a for loop instead, it would be much more simple than this

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

To answer your question, no, there's no way to "skip ahead" an arbitrary number of items within a foreach loop. Not being able to use a for loop is a bizarre requirement so I'm wondering if this is a puzzle and not a real problem. But I digress...

Remember that each access to an Excel property in C# is a COM call, so it is very expensive. You will get better performance (and make your programming easier) by pulling the entire range into an array:

object[,] data = rng.Value2 as object[,];

The array will contain strings (for text cells) and doubles (for numeric/date cells). THen you can use standard loops to navigate the array. When you;'re done, just put the data back:

rng.Value2 = data;

Upvotes: 1

Tushar
Tushar

Reputation: 3623

Looking at your question it seems that you need answer for "How to refer adjacent cells?"

For that you can use Offset() property in excel vba.

It works like this: Assume you are referring to Row no. 1 of column A. Now you want to refer same row but column D; you can use:

Range("Your_Range").Offset(0, 3).Select

For more on OFFSET function: https://msdn.microsoft.com/en-us/library/office/ff840060.aspx?f=255&MSPPError=-2147217396

Upvotes: 0

Related Questions