SpecialCells with SpecialCellType.Formulas of single cell is not correct

SpecialCells with SpecialCellType.Formulas of single cell is not correct

Posted by: yyoshimura on 7 December 2022, 4:33 pm EST

• 

  yyoshimura

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 7 December 2022, 4:33 pm EST

  Hi,

  We are using IRange.SpecialCells(SpecialCellType.Formulas) to get the cells with formulas in the range. With multiple cells range it is working as expected but with single cell the result is not as we expected. Please refer attachment for reproduction.

  Please help to investigate this issue. Thank you.

  Regards
  
  gcexcelperformance.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 11 December 2022, 3:23 pm EST

  Hello,

  Thank you for reporting this issue. We too can observe the issue and escalate this to the development team for investigation. We will let you know as soon as we get any update on this from their end.

  [Internal Tracking ID: DOCXLS-7228]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 13 December 2022, 9:24 pm EST - Updated 13 December 2022, 9:29 pm EST

  Hello Yukinori,

  As per the developers, This behavior is intended. Because Microsoft Excel looks in all cells in the worksheet of the specified Range if the Range is a cell.

  Sub Macro1()
  
  Cells.ClearContents
  
  Range(“A1”).FormulaR1C1 = “=1+2”
  
  Range(“A2”).FormulaR1C1 = “=2+3”
  
  Set a3FindFormulas = Range(“A3”).SpecialCells(xlCellTypeFormulas)
  
  MsgBox a3FindFormulas.Address
  
  End Sub

  f you want to check whether a range has formula cells, you need to use Range.Count and Range.HasFormula first.

  If Range.Count =1 then use Range.HasFormula . Otherwise, use Range.SpecialCells.

  private static IRange FindFormulaCellsExact(IRange lookIn)
  
  {
  
  if (lookIn == null)
  
  {
  
  return lookIn;
  
  }
  
  if (lookIn.Count < 2)
  
  {
  
  return lookIn.HasFormula ? lookIn : null;
  
  }
  
  return lookIn.SpecialCells(SpecialCellType.Formulas);
  
  }

  Regards,
  
  Prabhat Sharma.

  

  1652×552 46.1 KB

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 14 December 2022, 11:56 am EST

  Thank you for your detailed explanation and it really helps. We can try the provided method to get the exact result.