Unable to assign formula to cell range in Excel

Question

Some other code in the project that I'm trying to fix.

listO.Range(i, j).FormulaR1C1 = FormulaMatrix(i, j)

where FormulaMatrix(i, j) always a String value. Regardless of the random / test value that I am trying to use, it is assigned successfully, unless it is a formula, for example.

=IF(LENGTH([@Units])>0;[@SalesAmount]-[@DiscountAmount]0)

If I delete the = sign at the beginning of the formula, it is assigned correctly, but then it is useless because it is not a formula.

@Units , @SalesAmount , @DiscountAmount are the links / column names.

So, when assigning a formula, I get an exception HRESULT: 0x800A03EC . I looked in this answer for an explanation and followed some of the instructions. I decided that my problem is this: the problem arises due to the function entered in the cell and is trying to update another cell.

This post is also noted . I tried a completely different one (for example, I just put the formulas without = , and then ran it again and put equal signs), but the same problem.

I do not know how to approach this.

Answer 1

The error may come from your current data, respectively, the layout of the sheet. I suggest you check what is inside listO.Range(i, j).FormulaR1C1 before assigning the formula.

I had a case where the range already received the wrong data inside, and then it is strange, I don’t know why, I can’t assign a new formula.

If so, try to clear the range value and then assign the formula:

 listO.Range(i, j).FormulaR1C1 = "" listO.Range(i, j).FormulaR1C1 = FormulaMatrix(i, j) 

Answer 2

.formulalocal works! (So ​​far .formula , .value and .formular1c1 are not.)

I just started working with VB.NET and came up with a very similar problem. This was my simplified data first ( Table1 in Sheet1 ):

Then after applying the code below I had this:

All code for the form:

 Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim strAddress As String = "C:\Temp\SampleNew.xlsx" Dim list1 As Object Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click '~~> Add a New Workbook (IGNORING THE TWO DOT RULE) xlWorkBook = xlApp.Workbooks.Open(strAddress) '~~> Display Excel xlApp.Visible = True '~~> Set the relevant sheet that we want to work with xlWorkSheet = xlWorkBook.Sheets("Sheet1") With xlWorkSheet '~~> Change the range into a tabular format list1 = .ListObjects("Table1") End With list1.range(2, 4).formulalocal = "=IF(LEN([@Month])>5;[@Income]-[@MoneySpent];0)" '~~> Save the file xlApp.DisplayAlerts = False xlWorkBook.SaveAs(Filename:=strAddress, FileFormat:=51) xlApp.DisplayAlerts = True '~~> Close the File xlWorkBook.Close() '~~> Quit the Excel Application xlApp.Quit() '~~> Clean Up releaseObject(xlApp) releaseObject(xlWorkBook) End Sub '~~> Release the objects Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class 

@Siddharth Rout has helped many to build this code, as it owns this amazing site: http://www.siddharthrout.com/

Answer 3

The problem may be with your formula. Try it -

 =IF(LEN([@Units])>0,[@SalesAmount]-[@DiscountAmount],0) 

If this does not work, I would try using the .formula method instead of .formulaR1C1. Is there any reason in particular you are using R1C1 links?