Strange problem writing Excel formulas using .NET

[John Brock]

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?

 

[Dave Peterson]

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.

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?

 

[Dave Peterson]

Check your other post.

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.

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?

 

[Dave Peterson]

Sorry.

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

Check your other post.

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.

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]

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?

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.

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?