Strange problem writing Excel formulas using .NET   

I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:

	=VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)

On the completed workbook this turns into:

	=VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)

This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet).  But when I create this exact same formula using
.NET I get a #NAME? error!

There is nothing wrong with the text in the cell.  Forcing the
workbook to calculate using F9 doesn't help.  But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works!  (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).

I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on.  I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas.  Could that be what's
causing the problem in this case, when the formulas involve another
worksheet?  And if not that, then what might it be?
-- 
John Brock
jbrock@panix.com

date: Thu, 28 Aug 2008 04:06:33 +0000 (UTC)   author:   (John Brock)

Re: Strange problem writing Excel formulas using .NET   

I would guess it's because you're using a combination of R1C1 reference style
and A1 reference style.

  =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)

should be more like:

  =VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)

C1:C24 is column 1 to column 24.



John Brock wrote:
> 
> I have a .NET application that, among other things, creates Excel
> workbooks, and I have run into a very strange problem involving
> formulas on one worksheet that reference values on another worksheet.
> The text I write into, let's say, cell A25 on Sheet1 (using .NET)
> looks something like this:
> 
>         =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
> 
> On the completed workbook this turns into:
> 
>         =VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
> 
> This formula works fine when I type it in by hand, and I've been
> using the RC[] syntax in .NET without trouble for quite a while
> (although up until now all my formulas have only referenced their
> own worksheet).  But when I create this exact same formula using
> .NET I get a #NAME? error!
> 
> There is nothing wrong with the text in the cell.  Forcing the
> workbook to calculate using F9 doesn't help.  But if I double click
> on the cell as if I were going to add something to the formula,
> and then move away without adding anything, suddenly the formula
> works!  (There is further weirdness involving what happens if I
> now try to drag the apparently working formula across other cells,
> or when I save the workbook, but it's kind of hard to describe, so
> I won't go into it here unless someone wants to know).
> 
> I've been able to get around the problem for now by using a named
> range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
> about this, and I need to understand what is going on.  I have not
> been using the Formula or FormulaR1C1 properties to create formulas
> -- so far I've just been assigning text to a cell, and nothing more
> has seemed necessary, even for formulas.  Could that be what's
> causing the problem in this case, when the formulas involve another
> worksheet?  And if not that, then what might it be?
> --
> John Brock
> jbrock@panix.com

-- 

Dave Peterson

date: Thu, 28 Aug 2008 07:08:26 -0500   author:   Dave Peterson

Re: Strange problem writing Excel formulas using .NET   

Check your other post.

Dave Peterson wrote:
> 
> I would guess it's because you're using a combination of R1C1 reference style
> and A1 reference style.
> 
>   =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
> 
> should be more like:
> 
>   =VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
> 
> C1:C24 is column 1 to column 24.
> 
> John Brock wrote:
> >
> > I have a .NET application that, among other things, creates Excel
> > workbooks, and I have run into a very strange problem involving
> > formulas on one worksheet that reference values on another worksheet.
> > The text I write into, let's say, cell A25 on Sheet1 (using .NET)
> > looks something like this:
> >
> >         =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
> >
> > On the completed workbook this turns into:
> >
> >         =VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
> >
> > This formula works fine when I type it in by hand, and I've been
> > using the RC[] syntax in .NET without trouble for quite a while
> > (although up until now all my formulas have only referenced their
> > own worksheet).  But when I create this exact same formula using
> > .NET I get a #NAME? error!
> >
> > There is nothing wrong with the text in the cell.  Forcing the
> > workbook to calculate using F9 doesn't help.  But if I double click
> > on the cell as if I were going to add something to the formula,
> > and then move away without adding anything, suddenly the formula
> > works!  (There is further weirdness involving what happens if I
> > now try to drag the apparently working formula across other cells,
> > or when I save the workbook, but it's kind of hard to describe, so
> > I won't go into it here unless someone wants to know).
> >
> > I've been able to get around the problem for now by using a named
> > range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
> > about this, and I need to understand what is going on.  I have not
> > been using the Formula or FormulaR1C1 properties to create formulas
> > -- so far I've just been assigning text to a cell, and nothing more
> > has seemed necessary, even for formulas.  Could that be what's
> > causing the problem in this case, when the formulas involve another
> > worksheet?  And if not that, then what might it be?
> > --
> > John Brock
> > jbrock@panix.com
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson

date: Thu, 28 Aug 2008 07:38:08 -0500   author:   Dave Peterson

Re: Strange problem writing Excel formulas using .NET   

Sorry.

I didn't notice that the original was cross posted to all these groups.

Dave Peterson wrote:
> 
> Check your other post.
> 
> Dave Peterson wrote:
> >
> > I would guess it's because you're using a combination of R1C1 reference style
> > and A1 reference style.
> >
> >   =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
> >
> > should be more like:
> >
> >   =VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
> >
> > C1:C24 is column 1 to column 24.
> >
> > John Brock wrote:
> > >
> > > I have a .NET application that, among other things, creates Excel
> > > workbooks, and I have run into a very strange problem involving
> > > formulas on one worksheet that reference values on another worksheet.
> > > The text I write into, let's say, cell A25 on Sheet1 (using .NET)
> > > looks something like this:
> > >
> > >         =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
> > >
> > > On the completed workbook this turns into:
> > >
> > >         =VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
> > >
> > > This formula works fine when I type it in by hand, and I've been
> > > using the RC[] syntax in .NET without trouble for quite a while
> > > (although up until now all my formulas have only referenced their
> > > own worksheet).  But when I create this exact same formula using
> > > .NET I get a #NAME? error!
> > >
> > > There is nothing wrong with the text in the cell.  Forcing the
> > > workbook to calculate using F9 doesn't help.  But if I double click
> > > on the cell as if I were going to add something to the formula,
> > > and then move away without adding anything, suddenly the formula
> > > works!  (There is further weirdness involving what happens if I
> > > now try to drag the apparently working formula across other cells,
> > > or when I save the workbook, but it's kind of hard to describe, so
> > > I won't go into it here unless someone wants to know).
> > >
> > > I've been able to get around the problem for now by using a named
> > > range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
> > > about this, and I need to understand what is going on.  I have not
> > > been using the Formula or FormulaR1C1 properties to create formulas
> > > -- so far I've just been assigning text to a cell, and nothing more
> > > has seemed necessary, even for formulas.  Could that be what's
> > > causing the problem in this case, when the formulas involve another
> > > worksheet?  And if not that, then what might it be?
> > > --
> > > John Brock
> > > jbrock@panix.com
> >
> > --
> >
> > Dave Peterson
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson

date: Thu, 28 Aug 2008 07:42:48 -0500   author:   Dave Peterson

Re: Strange problem writing Excel formulas using .NET   

By golly, you seem to have nailed it!  I tried your suggestion and
it worked.  Thanks!  (It created a $A:$X reference in the formula
rather than A:X, which of interesting, but doesn't seem to matter).

I'd still like to understand what sort of state I was putting the
workbook into though.  It really was pretty strange.

I also wish I knew whether I really ought to be using the FormulaR1C1
property, instead of just writing strings beginning with "=" into
cells.  It wouldn't be that easy, since for efficiency I'm actually
slapping a two dimensional .NET Object array onto a large region,
rather than writing cells one at a time, so if I wanted to use
FormulaR1C1 I'd have to go back afterwards, find the formulas, and
do them all again the right way.  What a pain!  You think I can
continue to get away with what I am doing now?

In article ,
Dave Peterson   wrote:
>I would guess it's because you're using a combination of R1C1 reference style
>and A1 reference style.
>
>  =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
>
>should be more like:
>
>  =VLOOKUP(RC[7],'Sheet2'!c1:c24,6,FALSE)
>
>C1:C24 is column 1 to column 24.

>John Brock wrote:
>> 
>> I have a .NET application that, among other things, creates Excel
>> workbooks, and I have run into a very strange problem involving
>> formulas on one worksheet that reference values on another worksheet.
>> The text I write into, let's say, cell A25 on Sheet1 (using .NET)
>> looks something like this:
>> 
>>         =VLOOKUP(RC[7],'Sheet2'!A:X,6,FALSE)
>> 
>> On the completed workbook this turns into:
>> 
>>         =VLOOKUP(H25,'Sheet2'!A:X,6,FALSE)
>> 
>> This formula works fine when I type it in by hand, and I've been
>> using the RC[] syntax in .NET without trouble for quite a while
>> (although up until now all my formulas have only referenced their
>> own worksheet).  But when I create this exact same formula using
>> .NET I get a #NAME? error!
>> 
>> There is nothing wrong with the text in the cell.  Forcing the
>> workbook to calculate using F9 doesn't help.  But if I double click
>> on the cell as if I were going to add something to the formula,
>> and then move away without adding anything, suddenly the formula
>> works!  (There is further weirdness involving what happens if I
>> now try to drag the apparently working formula across other cells,
>> or when I save the workbook, but it's kind of hard to describe, so
>> I won't go into it here unless someone wants to know).
>> 
>> I've been able to get around the problem for now by using a named
>> range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
>> about this, and I need to understand what is going on.  I have not
>> been using the Formula or FormulaR1C1 properties to create formulas
>> -- so far I've just been assigning text to a cell, and nothing more
>> has seemed necessary, even for formulas.  Could that be what's
>> causing the problem in this case, when the formulas involve another
>> worksheet?  And if not that, then what might it be?
>> --
>> John Brock
>> jbrock@panix.com
>
>-- 
>
>Dave Peterson
-- 
John Brock
jbrock@panix.com

date: Fri, 29 Aug 2008 03:29:23 +0000 (UTC)   author:   (John Brock)