CODEWITHSUNDEEP
c#.netexcel-formulaexcel-interopvlookup
user1224954
user1224954

Reputation: 27

Exception from HRESULT: 0x800A03EC when trying to add formula r1c1 while creating workbook

I am creating an Excel file using C#.

The end result should be:

  1. Excel workbook with 5 sheets

  2. 1 sheet is generated using other 4 sheets.

  3. 4 sheets are generated from database

  4. All the formulas should be added to sheet 1 just in case the user wants to add data manually to other 4 sheets

I am stuck at:

After I create all the 4 sheets, I am trying to add formulae to sheet 1.

Formula Logic - Look for value of column A (all rows one by one) of sheet 1 (current) in sheet 2's column A, and get the value of column K of that row.

I am trying to add the below VLOOKUP to the 12th row and will copy the formula to all rows.

oRng = worksheet.Range["J12"];
oRng.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-9],'Sheet2'!$A:$K,11,0)),'',VLOOKUP(RC[-9],'Sheet2'!$A:$K,11,0)";

But I get the error:

Exception from HRESULT: 0x800A03EC

Upvotes: 0

Views: 3307

Answers (2)

Andy Brown
Andy Brown

Reputation: 19171

I believe you are:

  • Mixing A1 and R1C1 style (which it doesn't like)
  • using '' instead of "" (which it doesn't like)
  • using $ for absolute references on A1 style references in FormulaR1C1 (which it doesn't like)

edit it seems less likely in your case as you've said the formula works with direct input, but you may also have a locale issue where excel wants semicolon list separators and you are using commas, see my answer here for a solution to that. (end edit)

So try:

oRng.Formula = @"=IF(ISERROR(VLOOKUP(A12,Sheet2!$A:$K,11,0)),"""",VLOOKUP(A12,Sheet2!$A:$K,11,0))";

or

oRng.FormulaR1C1 = @"=IF(ISERROR(VLOOKUP(RC[-9],Sheet2!C1:C11,11,0)),"""",VLOOKUP(RC[-9],Sheet2!C1:C11,11,0))";

(If you don't want to prefix your string with @ then change all "" to \" inside the string)

Footnote

You can shorten your formula to this in later versions of Excel (2007 onwards):

oRng.Formula     = @"=IFERROR(VLOOKUP(A12,Sheet2!$A:$K,11,0),"""")"
oRng.FormulaR1C1 = @"=IFERROR(VLOOKUP(RC[-9],Sheet2!C1:C11,11,0),"""")"

Upvotes: 1

Zeugma
Zeugma

Reputation: 32125

Put the correct number of parenthesis in your Excel formula : you are missing one at the end.

Upvotes: 0

Related Questions