Breaking it into a piecemeal equation, although Indirect in general
does fine with Evaluate, doing a match on an indirect range causes an
error. I.e. MATCH(L$11,INDIRECT("BCs!r2c"&30&":r2c256",0),0) can't be
evaluated (occasionally seems to throw off a wrong answer rather than
a #VALUE error), but MATCH(L$11,BCs!AD2:IV2,0) works fine in the eval
function.

Any ideas on how to work around this problem? I can't avoid the
indirects.


Thanks again!

On Oct 23, 11:29 am, Nathan wrote:
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 -- Hide quoted text -

- Show quoted text -