jbrock
8/29/2008 3:29:00 AM
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 <48B6953A.7543682E@verizonXSPAM.net>,
Dave Peterson <petersod@verizonXSPAM.net> 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