CODEWITHSUNDEEP
c#excel
sigil
sigil

Reputation: 9546

having trouble getting range between two cells

I'm trying to determine whether or not any of the cells in a given range have a formula.

I'm using the following function:

    public bool noFormulas(Excel.Worksheet dataSheet)
    {
        Excel.Range beginRange=dataSheet.Cells[3, beginColumn];
        Excel.Range endRange=dataSheet.Cells[lastRow, endColumn];

        Excel.Range fullRange = dataSheet.Cells[beginRange,endRange];
        return fullRange.HasFormula == false;
    }

where I've declared the interop with:

using Excel = Microsoft.Office.Interop.Excel;

The problem is that when the statement assigning the value of fullRange is executed, I get this exception:

An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll
Additional information: Exception from HRESULT: 0x800A03EC

beginRange and endRange are both being successfully populated; shouldn't I be able to get a range based on its begin and end cells?

Upvotes: 2

Views: 556

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

As follow up from comments, you should change 

dataSheet.Cells[beginRange,endRange];

to

dataSheet.Range[beginRange,endRange];

Also instead fullRange.HasFormula you should loop through each cell and check whether any of it has formula (because in your original code it checks whether all cells in range has formula or not, and futhermore in situation when you have both cells with formulas and without formulas, fullRange.HasFormula throws exception). So, working code is:

public bool noFormulas(Excel.Worksheet dataSheet)
{
   Excel.Range beginRange = dataSheet.Cells[3, beginColumn];
   Excel.Range endRange = dataSheet.Cells[lastRow, endColumn];

   Excel.Range fullRange = dataSheet.Range[beginRange, endRange];

   foreach (Excel.Range c in fullRange) 
   {
      if (c.HasFormula)
      {
         return false;
      }
   }
   return true;
}

Upvotes: 2

Related Questions