By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,696 Members | 2,213 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,696 IT Pros & Developers. It's quick & easy.

Formula with Interop.excel do not work

P: 3
When i use the following code with interop.excel

Expand|Select|Wrap|Line Numbers
  1.             rng = wsDiagram.get_Range("B4", missing);
  2.  
  3.             rng.Formula = "=AVERAGE(B1:B3)"; // OR "=SUM(B1:B3)"; 
  4.             rng.Calculate();
  5.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. rng = wsDiagram.get_Range("B4", missing);
  3. rng.Formula = "=B1"; 
  4. //OR
  5. rng.Value2 = 43443512;
  6.  
  7.  
Can someone help me?

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

PS: i dont get any error messages
Dec 5 '08 #1
Share this Question
Share on Google+
5 Replies


P: 61
Try expanding the column before setting your data.
Dec 7 '08 #2

P: 3
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.
Dec 8 '08 #3

P: 61
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.

Expand|Select|Wrap|Line Numbers
  1. Range range22 = worksheet.get_Range("B1", "B3");       
  2. range22.Value2 = 6;
  3. rng = worksheet.get_Range("B4", Missing.Value);
  4. rng.Formula = "=AVERAGE(B1:B3)"; // OR "=SUM(B1:B3)";
  5. rng.Calculate();
Alternative try this:
Expand|Select|Wrap|Line Numbers
  1. // Try this...
  2. 1)rng.Formula = "=AVERAGE(B$1:B$3)";
  3. In this case, if you apply this formula to more than 1col,
  4. still the new cells also contain the same avg formula
  5. from B1 to B2 as.
  6. 2)rng.Formula = "=AVERAGE($B1:$B3)";
  7. In this case, if you apply this formula to more than 1col,
  8. the new cells contain the avg formula based on new cells.
  9. ie B5 holds "=AVG(B2:B4)"
Still if you are unable to solve the problem, plz do post
little more code.
Dec 8 '08 #4

P: 3
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)
Attached Images
File Type: jpg NAME.jpg (12.2 KB, 350 views)
Dec 8 '08 #5

P: 61
@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...
Expand|Select|Wrap|Line Numbers
  1. rng = worksheet.get_Range("G4", Missing.Value);
  2. Instead of printing below(in B4 cell) the selected range
  3. values, try to print horizontally and see whether it can
  4. help you.
Dec 9 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.