Not sure exactly why this fails but here are some possibilities:

When you use Application.Evaluate any unqualified range references (like
$A12) defaults to whatever is the active sheet.
Its better to either use fully qualified references (like Sheet1!$A12) or
use Worksheet("Sheet1").Evaluate to make all unqualified references refer to
sheet 1.

There are some other limitations of EVALUATE which you should be aware of:
see http://www.decisionmodels.com/calcsecretsh.htm

Secondly you are mixing R1C1 and A1 references in one formula which I would
have thought was not a good thing.

Thirdly the 256 character limit usually applies to the formula after all
references have been fully qualified by sheet names and the formula has been
internally converted to R1C1. Not sure if this applies to Evaluate ...

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Nathan" wrote in message
ups.com...
I actually had already shortened the formula below 256 characters...
posted the long version, sorry. No better results with this formula,
although it still works fine as a normal formula in Excel:

IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$T $178,'Product
Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs!
r2c"&30&":r2c256",0),0)+29&":r178c"&MATCH(L$11,IND IRECT("bcs!
r2c"&30&":r2c256",0),0)+29,0)))

217 characters.


On Oct 23, 11:18 am, Smallweed
wrote:
I think it's because you've gone over 256 characters.



"Nathan" wrote:
Hi,

I'm struggling to get a complex formula to be calculated by the
Evaluate method, called from a UDF. Something simple, like
"=sum(1,2,3)" works fine, but my complex formula returns a #VALUE
error. I have a formula that calculates to the following string:

=IF(OR($A12="",ProductName=""),"",SUMIF(BCs!$T$2:$ T$178,'Product
Detail'!$A12&"*",INDIRECT("BCs!r2c"&MATCH(L$11,IND IRECT("bcs!
r2c"&23+VLookupCode&":r2c256",0),0)+22+VLookupCode &":r178c"&MATCH(L
$11,INDIRECT("BCs!r2c"&23+VLookupCode&":r2c256",0) ,0)+22+VLookupCode,
0)))

When plugged into a cell, the formula works fine. When I use my UDF
(eval(etc.)), it fails. I've tried putting the string directly into
VBA with extra quotes as necessary--I can tell by using the 'Watch'
window that it's entered exactly the same as I'm entering it in the
spreadsheet.

I had a similar problem yesterday on another Evaluate and was able to
solve it by doing a more 'local' Evaluate (something like
Range("A1").Application.Evaluate(Range("A1").Value )... worked, while
just Evaluate(Range("A1").Value) did not).

I'm using Excel 2003 on WinXP. Thanks in advance for any ideas!- Hide
quoted text -

- Show quoted text -