Formula with Interop.excel do not work

[Toine DB]

When i use the following code with interop.excel

            rng = wsDiagram.get_Range("B4", missing);
 
            rng.Formula = "=AVERAGE(B1:B3)"; // OR "=SUM(B1:B3)"; 
            rng.Calculate();
 

The Cell shows #name?
When i hit the fomula bar suddenly te #name? goes away and the formula works.

Very strange, it happens with all the formulas in de Worksheet :-(
And the follwing code works fine filling the cells

 
rng = wsDiagram.get_Range("B4", missing);
rng.Formula = "=B1"; 
//OR
rng.Value2 = 43443512;
 
 

Can someone help me?

According to MSDN i'm doing it good, but it doesnt work.

PS: i dont get any error messages

[Ramk]

Try expanding the column before setting your data.

[Toine DB]

I dont see why Expanding should fix the problem with not calculating the Formula. But i tried it and it didnt work. :-(

It looks more like Excel thinks it is a String and later realizes that it is a Formula when i press enter in the Formula Bar.

I found out when i read a Excel sheet and write it back in an other one the a Formula just works fine. I think i forget some propertie of method ???

But i still have the same problem.

[Ramk]

I have a small sample which doesn't give the error reported by you.
Instead if the range B1 to B2 doesn't contain any value, then for the formula "=AVERAGE(B1:B3)" gave #DIV/0! error which is logical.

Range range22 = worksheet.get_Range("B1", "B3");       
range22.Value2 = 6;
rng = worksheet.get_Range("B4", Missing.Value);
rng.Formula = "=AVERAGE(B1:B3)"; // OR "=SUM(B1:B3)";
rng.Calculate();

Alternative try this:

 // Try this...
 1)rng.Formula = "=AVERAGE(B$1:B$3)";
In this case, if you apply this formula to more than 1col,
still the new cells also contain the same avg formula
from B1 to B2 as.
 2)rng.Formula = "=AVERAGE($B1:$B3)";
In this case, if you apply this formula to more than 1col,
the new cells contain the avg formula based on new cells.
ie B5 holds "=AVG(B2:B4)"

Still if you are unable to solve the problem, plz do post
little more code.

[Toine DB]

Thx for the info.

I tested it and i got exact the same problem whit your short code.
When the program opens the Sheet you made (your code) i get:

---------------------------------------
6
6
6
#name?

---------------------------------------

With you code i get the same problem, do you get the Formula good?

See my problem with my made workflow :-) (image)

[Ramk]

@Toine DB
I believe you are using Office2007. I am using Office2003.So, the same piece of code is working on Office2003 but not on Office2007.
Can you check the excel library which you are referring in your code!
Do try this...

rng = worksheet.get_Range("G4", Missing.Value);
Instead of printing below(in B4 cell) the selected range
values, try to print horizontally and see whether it can
help you.