Excel C# - using foreach with Range

  • Thread starter Thread starter John Putman
  • Start date Start date
J

John Putman

I'm just starting to convert an Excel addin that was
written in VBA to C#. I have two issues I am having
trouble with so far...

First.... I tried to copy the old VBA code that takes a
Range and then does a Find for a specific string within
the formulas of that Range. While the Find method is
finding cells with that string in the formula, it is
finding them outside of the specified range. For
instance, I grabbed the Selection range and did a Find on
it and it found a cell outside of the selection Range that
complied with the criteria. I need to know how to limit
it to just that certain Range.

In leiu of being able to do that, I tried iterating
through the Range one cell at a time, as I would have in
VBA using a foreach. It throws an error when I try
something like:
foreach (Excel.Range aCell in aCurrentRange)

The error says that 'Member cannot be found.' Perhaps C#
doesn't recognize the Range object as a collection like
VBA??? How can I iterate through a Range?

thanks for your help... I'm sure I'm just doing something
stupid on both counts ;)
 
Well I can answer the second part of the whole message, about the foreach.
In order to use the foreach statement, the collection must implement the
IEnumerable interface, which most all .NET collection/list type classes do,
but typically not interop.

You'll need to use an 'old fashioned 'for' loop for accessing each member.
I don't recall the exact Excel members, but it would look something like
this.

for (int i = 0; i < aCurrentRange.Count; i++)
{
Excel.Range aCell = aCurrentRange.Item(i);
}

Watch out for the indexes, most all .NET collections start at 0 but many of
the Office collections start at 1.

Cheers,

-Noah Coad
Microsoft MVP [.NET/C#]
 
Back
Top