Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Problem with inserting an array of formulas into a range
Hi!
I'm creating a spreadsheet (in ASP.NET) where I insert the values via arrays. I do this because I need to insert several hundreds rows. Inserting numbers works fine, but inserting formulas this way doesn't work. Example from my code: int[,] _data = new int[500,4]; // Then I do some stuff to fill the array with data string[,] _formulas = new string[500,1]; for(int _row = 1;_row<=500;_row++) _formulas[_row-1,0] = "=SUM(A"+_row.ToString()+":D"+_row.ToString() +")"; // Now I have an array of integers and an array of strings which contains my formulas // First I insert my integers which works fine Excel.Rang _range = _worksheet.get_Range("A1", _null); _range = _range.get_Resize(_rowsCount, 4); _range.set_Value(XlRangeValueDataType.xlRangeValue Default, _data); // Then I insert my formulas the same way, but it doesn't work as expected _range = _worksheet.get_Range("E1", _null); _range = _range.get_Resize(_rowsCount, 1); _range.set_Value(XlRangeValueDataType.xlRangeValue Default, _formulas); _range.Formula = _formulas; // This doesn't work either. The problem is that when I open the spreadsheet the cells with the formulas shown the formula and not the "calculated" formula, they show "=SUM(A1:D1)". The cell formats are all General. If I focus on the cell content and hit enter the formula gets evaluated and shows correct the number instead of the formula-text. If I open the "Evaluate formula" dialog it shows this message: "The cell currently being evaluated contains a constant". If I loop through all the cells and set their individual formulas it all works fine. But I don't want to do it like that - it takes much more time on the server when I insert the values into the cells one by one. Can anyone explain to me why the formulas don't get evaluated but only shows as "text"? Regards, Marius T. Krogh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions | |||
Isolate array formulas in a range | Excel Programming | |||
Error inserting formulas into a range object | Excel Programming | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions |